[Home] [Help]
PACKAGE BODY: APPS.GHR_CPDF_STATRPT
Source
1 PACKAGE BODY ghr_cpdf_statrpt AS
2 /* $Header: ghrcpdfs.pkb 120.7.12000000.3 2007/03/02 05:48:03 vmididho noship $ */
3
4 g_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE;
5 g_retained_pay_table_id pay_user_tables.user_table_id%TYPE;
6
7 PROCEDURE initialize_record
8 IS
9 l_proc varchar2(30) := 'initialize_record';
10 BEGIN
11 hr_utility.set_location('Entering:'||l_proc,5);
12 g_ghr_cpdf_temp.academic_discipline := NULL;
13 g_ghr_cpdf_temp.agency_code := NULL;
14 g_ghr_cpdf_temp.annuitant_indicator := NULL;
15 g_ghr_cpdf_temp.award_amount := NULL;
16 g_ghr_cpdf_temp.bargaining_unit_status := NULL;
17 g_ghr_cpdf_temp.benefit_amount := NULL;
18 g_ghr_cpdf_temp.citizenship := NULL;
19 g_ghr_cpdf_temp.creditable_military_service := NULL;
20 g_ghr_cpdf_temp.current_appointment_auth1 := NULL;
21 g_ghr_cpdf_temp.current_appointment_auth2 := NULL;
22 g_ghr_cpdf_temp.to_duty_station_code := NULL;
23 g_ghr_cpdf_temp.education_level := NULL;
24 g_ghr_cpdf_temp.effective_date := NULL;
25 g_ghr_cpdf_temp.employee_date_of_birth := NULL;
26 g_ghr_cpdf_temp.employee_first_name := NULL;
27 g_ghr_cpdf_temp.employee_last_name := NULL;
28 g_ghr_cpdf_temp.employee_middle_names := NULL;
29 g_ghr_cpdf_temp.from_national_identifier := NULL;
30 g_ghr_cpdf_temp.fegli := NULL;
31 g_ghr_cpdf_temp.fers_coverage := NULL;
32 g_ghr_cpdf_temp.first_action_la_code1 := NULL;
33 g_ghr_cpdf_temp.first_action_la_code2 := NULL;
34 g_ghr_cpdf_temp.first_noa_code := NULL;
35 g_ghr_cpdf_temp.flsa_category := NULL;
36 g_ghr_cpdf_temp.from_basic_pay := NULL;
37 g_ghr_cpdf_temp.from_duty_station_code := NULL;
38 g_ghr_cpdf_temp.from_grade_or_level := NULL;
39 g_ghr_cpdf_temp.from_locality_adj := NULL;
40 g_ghr_cpdf_temp.from_occ_code := NULL;
41 g_ghr_cpdf_temp.from_pay_table_id := NULL;
42 g_ghr_cpdf_temp.from_pay_basis := NULL;
43 g_ghr_cpdf_temp.from_pay_plan := NULL;
44 g_ghr_cpdf_temp.from_pay_rate_determinant := NULL;
45 g_ghr_cpdf_temp.from_retirement_coverage := NULL;
46 g_ghr_cpdf_temp.from_step_or_rate := NULL;
47 g_ghr_cpdf_temp.from_total_salary := NULL;
48 g_ghr_cpdf_temp.from_work_schedule := NULL;
49 g_ghr_cpdf_temp.frozen_service := NULL;
50 g_ghr_cpdf_temp.functional_class := NULL;
51 g_ghr_cpdf_temp.handicap_code := NULL;
52 g_ghr_cpdf_temp.health_plan := NULL;
53 g_ghr_cpdf_temp.individual_group_award := NULL;
54 g_ghr_cpdf_temp.organizational_component := NULL;
55 g_ghr_cpdf_temp.pay_status := NULL;
56 g_ghr_cpdf_temp.personnel_office_id := NULL;
57 g_ghr_cpdf_temp.position_occupied := NULL;
58 g_ghr_cpdf_temp.race_national_origin := NULL;
59 g_ghr_cpdf_temp.rating_of_record := NULL;
60 g_ghr_cpdf_temp.rating_of_record_level := NULL;
61 g_ghr_cpdf_temp.rating_of_record_pattern := NULL;
62 g_ghr_cpdf_temp.rating_of_record_period_ends := NULL;
63 g_ghr_cpdf_temp.retained_grade_or_level := NULL;
64 g_ghr_cpdf_temp.retained_pay_plan := NULL;
65 g_ghr_cpdf_temp.retained_step_or_rate := NULL;
66 g_ghr_cpdf_temp.retirement_plan := NULL;
67 g_ghr_cpdf_temp.second_noa_code := NULL;
68 g_ghr_cpdf_temp.service_comp_date := NULL;
69 g_ghr_cpdf_temp.sex := NULL;
70 g_ghr_cpdf_temp.supervisory_status := NULL;
71 g_ghr_cpdf_temp.tenure := NULL;
72 g_ghr_cpdf_temp.to_basic_pay := NULL;
73 g_ghr_cpdf_temp.to_grade_or_level := NULL;
74 g_ghr_cpdf_temp.to_locality_adj := NULL;
75 g_ghr_cpdf_temp.to_national_identifier := NULL;
76 g_ghr_cpdf_temp.to_occ_code := NULL;
77 g_ghr_cpdf_temp.to_pay_basis := NULL;
78 g_ghr_cpdf_temp.to_pay_plan := NULL;
79 g_ghr_cpdf_temp.to_pay_rate_determinant := NULL;
80 g_ghr_cpdf_temp.to_pay_table_id := NULL;
81 g_ghr_cpdf_temp.to_retention_allowance := NULL;
82 g_ghr_cpdf_temp.to_staffing_differential := NULL;
83 g_ghr_cpdf_temp.to_step_or_rate := NULL;
84 g_ghr_cpdf_temp.to_supervisory_differential := NULL;
85 g_ghr_cpdf_temp.to_total_salary := NULL;
86 g_ghr_cpdf_temp.to_work_schedule := NULL;
87 g_ghr_cpdf_temp.veterans_preference := NULL;
88 g_ghr_cpdf_temp.veterans_status := NULL;
89 g_ghr_cpdf_temp.year_degree_attained := NULL;
90
91 g_duty_station_id := NULL;
92 g_ghr_cpdf_temp.SCD_retirement := NULL;
93 g_ghr_cpdf_temp.SCD_rif := NULL;
94 g_ghr_cpdf_temp.position_title := NULL;
95 g_ghr_cpdf_temp.name_title := NULL;
96
97 END initialize_record;
98
99 PROCEDURE cleanup_table
100 IS
101 l_proc varchar2(30) := 'cleanup_table';
102 BEGIN
103 hr_utility.set_location('Entering:'||l_proc,5);
104 DELETE FROM ghr_cpdf_temp
105 WHERE report_type = 'STATUS'
106 AND session_id = userenv('SESSIONID')
107 ;
108 END cleanup_table;
109
110 PROCEDURE get_appointment_date (p_person_id IN NUMBER
111 ,p_report_date IN DATE
112 ,p_appointment_date OUT NOCOPY DATE) IS
113
114 -- Cursor modified for Performance changes
115 /* CURSOR cur_per IS
116 SELECT per.hire_date
117 FROM per_people_v per
118 WHERE per.person_id = p_person_id; */
119 -- Bug 3742271, 3757124 - Added p_person_id in the following condition.
120 CURSOR cur_per IS
121 SELECT
122 DECODE(PER.CURRENT_EMPLOYEE_FLAG, 'Y', PPS.DATE_START, DECODE(PER.CURRENT_NPW_FLAG, 'Y', PPP.DATE_START, NULL)) hire_date
123 FROM
124 PER_PEOPLE_F PER ,
125 PER_PERIODS_OF_SERVICE PPS ,
126 PER_PERIODS_OF_PLACEMENT PPP
127 WHERE
128 PPS.PERSON_ID (+) = PER.PERSON_ID AND
129 PPP.PERSON_ID (+) = PER.PERSON_ID AND
130 PER.PERSON_ID = p_person_id AND
131 (
132 (PER.EMPLOYEE_NUMBER IS NULL) OR
133 (PER.EMPLOYEE_NUMBER IS NOT NULL AND
134 PPS.DATE_START = (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID = PER.PERSON_ID AND
135 PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND
136 ((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL AND
137 PPP.DATE_START = (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND
138 PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)));
139
140 BEGIN
141 INSERT INTO fnd_sessions
142 (session_id
143 ,effective_date)
144 VALUES
145 (userenv('sessionid')
146 ,p_report_date);
147
148 --
149 FOR cur_per_rec IN cur_per LOOP
150 p_appointment_date := cur_per_rec.hire_date;
151 END LOOP;
152
153 DELETE FROM fnd_sessions
154 WHERE session_id = userenv('sessionid');
155
156 END get_appointment_date;
157 --
158 PROCEDURE get_from_history_asgnei
159 (
160 p_sr_assignment_id IN NUMBER
161 ,p_sr_report_date IN DATE
162 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
163 )
164 IS
165 l_proc varchar2(30) := 'get_from_history_asgnei';
166 l_ASGNEI_DATA PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
167 l_ASGNEI_DATA_INIT PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
168 BEGIN
169 hr_utility.set_location('Entering:'||l_proc,5);
170
171 l_ASGNEI_DATA := l_ASGNEI_DATA_INIT;
172 -- Begin Bug# 4168162
173 g_message_name := 'Assignment EIT: Assigment RPA';
174 -- End Bug# 4168162
175 GHR_HISTORY_FETCH.fetch_asgei(
176 p_assignment_id => p_sr_assignment_id,
177 p_information_type => 'GHR_US_ASG_SF52',
178 p_date_effective => p_sr_report_date,
179 p_asg_ei_data => l_ASGNEI_DATA
180 );
181 p_sr_ghr_cpdf_temp.annuitant_indicator := l_ASGNEI_DATA.AEI_INFORMATION5;
182 p_sr_ghr_cpdf_temp.to_step_or_rate := l_ASGNEI_DATA.AEI_INFORMATION3;
183 p_sr_ghr_cpdf_temp.to_pay_rate_determinant := l_ASGNEI_DATA.AEI_INFORMATION6;
184 p_sr_ghr_cpdf_temp.tenure := l_ASGNEI_DATA.AEI_INFORMATION4;
185 p_sr_ghr_cpdf_temp.to_work_schedule := l_ASGNEI_DATA.AEI_INFORMATION7;
186 -- FWFA Changes Get pay table id also
187 p_sr_ghr_cpdf_temp.to_pay_table_id := l_ASGNEI_DATA.AEI_INFORMATION9;
188 -- FWFA Changes
189
190 END get_from_history_asgnei;
191
192
193 PROCEDURE get_from_history_people
194 (
195 p_sr_person_id IN NUMBER
196 ,p_sr_report_date IN DATE
197 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
198 )
199 IS
200 l_proc varchar2(30) := 'get_from_history_people';
201 CURSOR PEOPLE_CUR IS
202 SELECT SEX,
203 DATE_OF_BIRTH,
204 NATIONAL_IDENTIFIER
205 FROM PER_PEOPLE_F
206 WHERE (TRUNC(p_sr_report_date) between effective_start_date and
207 effective_end_date) AND
208 PERSON_ID = g_person_id;
209
210 l_PEOPLE_REC PEOPLE_CUR%ROWTYPE;
211
212 BEGIN
213 hr_utility.set_location('Entering:'||l_proc,5);
214 OPEN PEOPLE_CUR;
215
216 FETCH PEOPLE_CUR INTO l_PEOPLE_REC;
217
218 IF PEOPLE_CUR%FOUND
219 THEN
220 p_sr_ghr_cpdf_temp.sex := l_PEOPLE_REC.SEX;
221 p_sr_ghr_cpdf_temp.employee_date_of_birth := l_PEOPLE_REC.DATE_OF_BIRTH;
222 p_sr_ghr_cpdf_temp.to_national_identifier :=
223 SUBSTR(l_PEOPLE_REC.NATIONAL_IDENTIFIER,1,3) ||
224 SUBSTR(l_PEOPLE_REC.NATIONAL_IDENTIFIER,5,2) ||
225 SUBSTR(l_PEOPLE_REC.NATIONAL_IDENTIFIER,8,4);
226 END IF;
227
228 CLOSE PEOPLE_CUR;
229
230 END get_from_history_people;
231
232 PROCEDURE get_from_history_ancrit
233 (
234 p_sr_person_id IN NUMBER
235 ,p_sr_report_date IN DATE
236 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
237 )
238 IS
239 l_proc varchar2(30) := 'get_from_history_ancrit';
240 l_ANCRIT_REC ghr_api.special_information_type;
241 l_emp_number per_people_f.employee_number%TYPE;
242 CURSOR c_per IS
243 SELECT per.employee_number
244 FROM per_people_f per
245 WHERE per.person_id = p_sr_person_id
246 AND NVL(p_sr_report_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
247 AND per.effective_end_date;
248 BEGIN
249
250 -- bug 749386 use ghr_api.return_education_details and ghr_api.return_special_information
251 hr_utility.set_location('Entering:'||l_proc,5);
252 -- Begin Bug# 4168162
253 g_message_name := 'Special Info: Education Dtls';
254 -- End Bug# 4168162
255 ghr_api.return_education_details(p_person_id => p_sr_person_id,
256 p_effective_date => p_sr_report_date,
257 p_education_level => p_sr_ghr_cpdf_temp.education_level,
258 p_academic_discipline => p_sr_ghr_cpdf_temp.academic_discipline,
259 p_year_degree_attained => p_sr_ghr_cpdf_temp.year_degree_attained);
260
261 -- Begin Bug# 4168162
262 g_message_name := 'Special Info: Perf Appraisal';
263 -- End Bug# 4168162
264 ghr_cpdf_dynrpt.get_per_sit_perf_appraisal(p_sr_person_id
265 ,p_sr_report_date
266 ,p_sr_ghr_cpdf_temp.rating_of_record_level
267 ,p_sr_ghr_cpdf_temp.rating_of_record_pattern
268 ,p_sr_ghr_cpdf_temp.rating_of_record_period_ends);
269
270 /***** Commented 2003405
271 ghr_api.return_special_information(p_person_id => p_sr_person_id,
272 p_structure_name => 'US Fed Perf Appraisal',
273 p_effective_date => p_sr_report_date,
274 p_special_info => l_ancrit_rec);
275
276 IF l_ancrit_rec.object_version_number IS NOT NULL THEN
277 p_sr_ghr_cpdf_temp.rating_of_record_level := l_ANCRIT_REC.SEGMENT5;
278 p_sr_ghr_cpdf_temp.rating_of_record_pattern := l_ANCRIT_REC.SEGMENT4;
279 p_sr_ghr_cpdf_temp.rating_of_record_period_ends := fnd_date.canonical_to_date(l_ANCRIT_REC.SEGMENT6);
280 ELSE -- Generate entry in PROCESS_LOG
281 OPEN c_per;
282 FETCH c_per INTO l_emp_number;
283 CLOSE c_per;
284 ghr_mto_int.log_message(p_procedure => 'No US Fed Perf Appraisal Info',
285 p_message => 'Employee number ' || l_emp_number ||
286 ' does not have US Fed Perf Appraisal ' ||
287 'on ' || TO_CHAR(p_sr_report_date, 'DD-MON-YYYY'));
288 END IF;
289 ********* Bug 2003405******/
290
291 END get_from_history_ancrit;
292
293 PROCEDURE get_from_history_peopei
294 (
295 p_sr_person_id IN NUMBER
296 ,p_sr_report_date IN DATE
297 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
298 )
299 IS
300 l_proc varchar2(30) := 'get_from_history_peopei';
301 l_PEOPEI_DATA PER_PEOPLE_EXTRA_INFO%ROWTYPE;
302 l_PEOPEI_DATA_INIT PER_PEOPLE_EXTRA_INFO%ROWTYPE;
303 l_type_of_employment per_people_extra_info.pei_information4%TYPE;
304 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
305 BEGIN
306 hr_utility.set_location('Entering:'||l_proc,5);
307
308
309 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
310 -- Begin Bug# 4168162
311 g_message_name := 'Person EIT: Uniformed Serivces';
312 -- End Bug# 4168162
313 GHR_HISTORY_FETCH.fetch_peopleei(
314 p_person_id => p_sr_person_id,
315 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
316 p_date_effective => p_sr_report_date,
317 p_per_ei_data => l_PEOPEI_DATA
318 );
319 p_sr_ghr_cpdf_temp.creditable_military_service := SUBSTR(l_PEOPEI_DATA.PEI_INFORMATION5,1,4);
320
321 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
322 -- Begin Bug# 4168162
323 g_message_name := 'Person EIT: Separation, Retire';
324 -- End Bug# 4168162
325 GHR_HISTORY_FETCH.fetch_peopleei(
326 p_person_id => p_sr_person_id,
327 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
328 p_date_effective => p_sr_report_date,
329 p_per_ei_data => l_PEOPEI_DATA
330 );
331 p_sr_ghr_cpdf_temp.frozen_service := SUBSTR(l_PEOPEI_DATA.PEI_INFORMATION5,1,4);
332 p_sr_ghr_cpdf_temp.fers_coverage := l_PEOPEI_DATA.PEI_INFORMATION3;
333
334
335 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
336 -- Begin Bug# 4168162
337 g_message_name := 'Person EIT: Person RPA';
338 -- End Bug# 4168162
339 GHR_HISTORY_FETCH.fetch_peopleei(
340 p_person_id => p_sr_person_id,
341 p_information_type => 'GHR_US_PER_SF52',
342 p_date_effective => p_sr_report_date,
343 p_per_ei_data => l_PEOPEI_DATA
344 );
345
346 p_sr_ghr_cpdf_temp.veterans_preference := l_PEOPEI_DATA.PEI_INFORMATION4;
347 p_sr_ghr_cpdf_temp.veterans_status := l_PEOPEI_DATA.PEI_INFORMATION6;
348 p_sr_ghr_cpdf_temp.citizenship := l_PEOPEI_DATA.PEI_INFORMATION3;
349
350
351 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
352 -- Begin Bug# 4168162
353 g_message_name := 'Person EIT: Person Group1';
354 -- End Bug# 4168162
355 GHR_HISTORY_FETCH.fetch_peopleei(
356 p_person_id => p_sr_person_id,
357 p_information_type => 'GHR_US_PER_GROUP1',
358 p_date_effective => p_sr_report_date,
359 p_per_ei_data => l_PEOPEI_DATA
360 );
361 p_sr_ghr_cpdf_temp.current_appointment_auth1 := l_PEOPEI_DATA.PEI_INFORMATION8;
362 p_sr_ghr_cpdf_temp.current_appointment_auth2 := l_PEOPEI_DATA.PEI_INFORMATION9;
363 p_sr_ghr_cpdf_temp.race_national_origin := l_PEOPEI_DATA.PEI_INFORMATION5;
364 p_sr_ghr_cpdf_temp.handicap_code := l_PEOPEI_DATA.PEI_INFORMATION11;
365 l_type_of_employment := l_PEOPEI_DATA.PEI_INFORMATION4;
366
367 -- bug 749190 Use FUNCTION ghr_pc_basic_pay.get_retained_grade_details instead of
368 -- GHR_HISTORY_FETCH.fetch_peopleei
369 -- do not woory if it didn't return anything!
370 IF p_sr_ghr_cpdf_temp.to_pay_rate_determinant IN ('A','B','E','F','U','V') THEN
371 BEGIN
372 -- Begin Bug# 4168162
373 g_message_name := 'Person EIT: RG Details';
374 -- End Bug# 4168162
375 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
376 p_person_id => p_sr_person_id,
377 p_effective_date => p_sr_report_date
378 );
379 --- added for bug 3834462 Madhuri, store Retained Pay Basis value
380 --- Start of fix
381 p_sr_ghr_cpdf_temp.to_pay_basis := l_retained_grade_rec.pay_basis;
382 -- End of Bug fix
383 p_sr_ghr_cpdf_temp.retained_pay_plan := l_retained_grade_rec.pay_plan;
384 p_sr_ghr_cpdf_temp.retained_grade_or_level := l_retained_grade_rec.grade_or_level;
385 p_sr_ghr_cpdf_temp.retained_step_or_rate := l_retained_grade_rec.step_or_rate;
386 g_retained_pay_table_id := l_retained_grade_rec.user_table_id;
387 EXCEPTION
388 WHEN ghr_pay_calc.pay_calc_message THEN
389 --l_message_name := 'Person EIT - Retained Grade';
390 RAISE;
391 END;
392 END IF;
393
394 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
395 -- Begin Bug# 4168162
396 g_message_name := 'Person EIT: Person SCD Info';
397 -- End Bug# 4168162
398 GHR_HISTORY_FETCH.fetch_peopleei(
399 p_person_id => p_sr_person_id,
400 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
401 p_date_effective => p_sr_report_date,
402 p_per_ei_data => l_PEOPEI_DATA
403 );
404 -- SVC may be changed w/ resolution of type/length conversion
405 p_sr_ghr_cpdf_temp.service_comp_date :=
406 fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION3);
407
408 -- SCD RIF and Retirement dates
409 p_sr_ghr_cpdf_temp.SCD_rif :=
410 fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION5);
411 p_sr_ghr_cpdf_temp.SCD_retirement :=
412 fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION7);
413 -- EHRI changes
414
415 -- CPDF EDITS FOR CREDITABLE MILITARY SERVICE
416 -- October date specified per requirements
417 -- Suggested Fix for 4060669
418 IF NVL(p_sr_ghr_cpdf_temp.annuitant_indicator,'9') <> '9' OR
419 g_appointment_date < to_date('1986/10/01','YYYY/MM/DD')
420 THEN
421 p_sr_ghr_cpdf_temp.creditable_military_service := ' ';
422 END IF;
423
424 IF (SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,1,1) < '0' OR
425 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,2,1) < '0' OR
426 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,3,1) < '0' OR
427 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,4,1) < '0' OR
428 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,1,1) > '9' OR
429 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,2,1) > '9' OR
430 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,3,1) > '9' OR
431 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,4,1) > '9' ) AND
432 p_sr_ghr_cpdf_temp.creditable_military_service IS NOT NULL AND
433 p_sr_ghr_cpdf_temp.creditable_military_service <> ' '
434 THEN
435 p_sr_ghr_cpdf_temp.creditable_military_service := '0000';
436 END IF;
437
438 -- CPDF EDITS FOR FROZEN SERVICE
439 -- October date specified per requirements
440 -- use retirement plan, not fers coverage per raj/john
441 IF p_sr_ghr_cpdf_temp.retirement_plan NOT IN('K','L','M','N','C','E') OR
442 g_appointment_date < to_date('1986/10/01','YYYY/MM/DD')
443 THEN
444 p_sr_ghr_cpdf_temp.frozen_service := ' ';
445 END IF;
446
447
448 -- CPDF EDITS FOR FERS COVERAGE
449 -- use retirement plan, not fers coverage per raj/john
450 IF p_sr_ghr_cpdf_temp.retirement_plan NOT IN('K','L','M','N')
451 THEN
452 p_sr_ghr_cpdf_temp.fers_coverage := ' ';
453 END IF;
454
455 -- CPDF EDITS FOR RETAINED ...
456 IF p_sr_ghr_cpdf_temp.to_pay_rate_determinant
457 NOT IN ('A','B','E','F','U','V')
458 THEN
459 p_sr_ghr_cpdf_temp.retained_pay_plan := NULL;
460 p_sr_ghr_cpdf_temp.retained_grade_or_level := NULL;
461 p_sr_ghr_cpdf_temp.retained_step_or_rate := NULL;
462 -- Bug 3834462 fix Madhuri
463 -- NULL this out incase the PRD is not in above list,
464 -- pay basis can be picked now from valid grade info instead of the retained grade details.
465 p_sr_ghr_cpdf_temp.to_pay_basis := NULL;
466 END IF;
467
468 -- CPDF EDITS FOR PAY_STATUS
469
470 -- pay status is derived from type of employment per logic provided
471 -- by john z.
472
473 IF l_type_of_employment
474 IN ('1','2','3','4','5','6','7','C','D','E','G','H','J','W')
475 THEN
476 p_sr_ghr_cpdf_temp.pay_status := 'P';
477 ELSIF l_type_of_employment = 'F'
478 THEN
479 p_sr_ghr_cpdf_temp.pay_status := 'N';
480 ELSE
481 p_sr_ghr_cpdf_temp.pay_status := l_type_of_employment;
482 END IF;
483
484 -- Fetching Race and ethnicity category
485 l_PEOPEI_DATA :=NULL;
486 -- Begin Bug# 4168162
487 g_message_name := 'Person EIT: Ethnicity, Race';
488 -- End Bug# 4168162
489 ghr_history_fetch.fetch_peopleei
490 (p_person_id => p_sr_person_id,
491 p_information_type => 'GHR_US_PER_ETHNICITY_RACE',
492 p_date_effective => p_sr_report_date,
493 p_per_ei_data => l_PEOPEI_DATA
494 );
495 p_sr_ghr_cpdf_temp.race_ethnic_info := NULL;
496 -- Populate Race only if atleast one data segment is entered.
497 IF l_PEOPEI_DATA.pei_information3 IS NOT NULL OR
498 l_PEOPEI_DATA.pei_information4 IS NOT NULL OR
499 l_PEOPEI_DATA.pei_information5 IS NOT NULL OR
500 l_PEOPEI_DATA.pei_information6 IS NOT NULL OR
501 l_PEOPEI_DATA.pei_information7 IS NOT NULL OR
502 l_PEOPEI_DATA.pei_information8 IS NOT NULL THEN
503 p_sr_ghr_cpdf_temp.race_ethnic_info := NVL(l_PEOPEI_DATA.pei_information3,'0') || NVL(l_PEOPEI_DATA.pei_information4,'0') || NVL(l_PEOPEI_DATA.pei_information5,'0') ||
504 NVL(l_PEOPEI_DATA.pei_information6,'0') || NVL(l_PEOPEI_DATA.pei_information7,'0') || NVL(l_PEOPEI_DATA.pei_information8,'0');
505 END IF;
506 -- End Bug 4714292 EHRI Reports Changes for EOY 05
507
508 END get_from_history_peopei;
509
510 PROCEDURE get_from_history_posiei
511 (
512 p_sr_position_id IN NUMBER
513 ,p_sr_report_date IN DATE
514 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
515 )
516 IS
517 l_proc varchar2(30) := 'get_from_history_posiei';
518 l_POSIEI_DATA PER_POSITION_EXTRA_INFO%ROWTYPE;
519 l_POSIEI_DATA_INIT PER_POSITION_EXTRA_INFO%ROWTYPE;
520 BEGIN
521 hr_utility.set_location('Entering:'||l_proc,5);
522
523 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
524 -- Begin Bug# 4168162
525 g_message_name := 'Position EIT: Position Group1';
526 -- End Bug# 4168162
527 GHR_HISTORY_FETCH.fetch_positionei(
528 p_position_id => p_sr_position_id,
529 p_information_type => 'GHR_US_POS_GRP1',
530 p_date_effective => p_sr_report_date,
531 p_pos_ei_data => l_POSIEI_DATA
532 );
533 p_sr_ghr_cpdf_temp.organizational_component := l_POSIEI_DATA.POEI_INFORMATION5;
534 p_sr_ghr_cpdf_temp.personnel_office_id := l_POSIEI_DATA.POEI_INFORMATION3;
535 p_sr_ghr_cpdf_temp.functional_class := l_POSIEI_DATA.POEI_INFORMATION11;
536 p_sr_ghr_cpdf_temp.supervisory_status := l_POSIEI_DATA.POEI_INFORMATION16;
537 p_sr_ghr_cpdf_temp.flsa_category := l_POSIEI_DATA.POEI_INFORMATION7;
538 p_sr_ghr_cpdf_temp.bargaining_unit_status := l_POSIEI_DATA.POEI_INFORMATION8;
539
540
541 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
542 -- Begin Bug# 4168162
543 g_message_name := 'Position EIT: Valid Grade';
544 -- End Bug# 4168162
545 GHR_HISTORY_FETCH.fetch_positionei(
546 p_position_id => p_sr_position_id,
547 p_information_type => 'GHR_US_POS_VALID_GRADE',
548 p_date_effective => p_sr_report_date,
549 p_pos_ei_data => l_POSIEI_DATA
550 );
551 -- Added this condition for Bug 3834462 Fix (Madhuri)
552 IF p_sr_ghr_cpdf_temp.to_pay_basis is NULL THEN
553 p_sr_ghr_cpdf_temp.to_pay_basis := l_POSIEI_DATA.POEI_INFORMATION6;
554 END IF;
555 -- Added this condition for Bug 3834462 Fix (Madhuri)
556 if (l_POSIEI_DATA.POEI_INFORMATION5 is not null) then
557 p_sr_ghr_cpdf_temp.to_pay_table_id := l_POSIEI_DATA.POEI_INFORMATION5;
558 end if;
559
560 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
561 -- Begin Bug# 4168162
562 g_message_name := 'Position EIT: Position Group2';
563 -- End Bug# 4168162
564 GHR_HISTORY_FETCH.fetch_positionei(
565 p_position_id => p_sr_position_id,
566 p_information_type => 'GHR_US_POS_GRP2',
567 p_date_effective => p_sr_report_date,
568 p_pos_ei_data => l_POSIEI_DATA
569 );
570 p_sr_ghr_cpdf_temp.position_occupied := l_POSIEI_DATA.POEI_INFORMATION3;
571
572 -- CPDF EDITS FOR PAY TABLE ID
573 IF p_sr_ghr_cpdf_temp.to_pay_rate_determinant NOT IN ('5','6','E','F','M')
574 THEN
575 p_sr_ghr_cpdf_temp.to_pay_table_id := ' ';
576 END IF;
577
578 END get_from_history_posiei;
579
580 PROCEDURE get_from_history_gradef
581 (
582 p_sr_grade_id IN NUMBER
583 ,p_sr_report_date IN DATE
584 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
585 )
586 IS
587 -- start_date_active and end_date_active on the PER_GRADE_DEFINITIONS
588 -- table WAS NOT considered as a query criteria because all rows on
589 -- GHRDEV16 had null values for both columns.
590 l_proc varchar2(30) := 'get_from_history_gradef';
591 CURSOR GRADEFCUR IS
592 SELECT SEGMENT1,
593 SEGMENT2
594 FROM PER_GRADE_DEFINITIONS
595 WHERE GRADE_DEFINITION_ID =
596 (SELECT MAX(GRADE_DEFINITION_ID)
597 FROM PER_GRADES
598 WHERE GRADE_ID = g_grade_id);
599
600 l_GRADEFREC GRADEFCUR%ROWTYPE;
601 BEGIN
602 hr_utility.set_location('Entering:'||l_proc,5);
603
604 OPEN GRADEFCUR;
605
606 FETCH GRADEFCUR INTO l_GRADEFREC;
607
608 -- Pay Plan to be changed w/ resolution of type/length conversion
609
610 IF GRADEFCUR%FOUND
611 THEN
612 p_sr_ghr_cpdf_temp.to_pay_plan := substr(l_GRADEFREC.SEGMENT1,1,2);
613 p_sr_ghr_cpdf_temp.to_grade_or_level := l_GRADEFREC.SEGMENT2;
614 END IF;
615
616 CLOSE GRADEFCUR;
617 END get_from_history_gradef;
618
619 PROCEDURE get_from_history_jobdef
620 (
621 p_sr_job_id IN NUMBER
622 ,p_sr_report_date IN DATE
623 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
624 )
625 IS
626 l_proc varchar2(30) := 'get_from_history_jobdef';
627 CURSOR JOBDEF_CUR IS
628 SELECT SEGMENT1
629 FROM PER_JOB_DEFINITIONS
630 WHERE JOB_DEFINITION_ID =
631 (SELECT JOB_DEFINITION_ID
632 FROM PER_JOBS
633 WHERE JOB_ID = g_job_id);
634
635 -- Declared record despite a "one column query" for future maintenance
636 l_JOBDEF_REC JOBDEF_CUR%ROWTYPE;
637 BEGIN
638 hr_utility.set_location('Entering:'||l_proc,5);
639
640 OPEN JOBDEF_CUR;
641
642 FETCH JOBDEF_CUR INTO l_JOBDEF_REC;
643
644 IF JOBDEF_CUR%FOUND
645 THEN
646 p_sr_ghr_cpdf_temp.to_occ_code := l_JOBDEF_REC.SEGMENT1;
647 END IF;
648
649 CLOSE JOBDEF_CUR;
650
651 END get_from_history_jobdef;
652
653 PROCEDURE get_from_history_dutsta
654 (
655 p_sr_location_id IN NUMBER
656 ,p_sr_report_date IN DATE
657 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
658 )
659 IS
660 l_proc varchar2(30) := 'get_from_history_dutsta';
661
662 CURSOR DUTSTACUR IS
663 SELECT DUTY_STATION_CODE, DUTY_STATION_ID
664 FROM GHR_DUTY_STATIONS_F
665 WHERE trunc(p_sr_report_date) between effective_start_date and
666 nvl(effective_end_date, p_sr_report_date)
667 AND DUTY_STATION_ID =
668 (SELECT LEI_INFORMATION3
669 FROM HR_LOCATION_EXTRA_INFO
670 WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
671 AND LOCATION_ID = g_location_id);
672
673 -- Declared record despite a "one column query" for future maintenance
674 l_DUTSTAREC DUTSTACUR%ROWTYPE;
675 BEGIN
676 hr_utility.set_location('Entering:'||l_proc,5);
677 -- Begin Bug# 4168162
678 g_message_name := 'Duty Station Details';
679 -- End Bug# 4168162
680 OPEN DUTSTACUR;
681
682 FETCH DUTSTACUR INTO l_DUTSTAREC;
683
684 IF DUTSTACUR%FOUND
685 THEN
686 p_sr_ghr_cpdf_temp.to_duty_station_code := l_DUTSTAREC.DUTY_STATION_CODE;
687 g_duty_station_id := l_DUTSTAREC.DUTY_STATION_ID;
688 END IF;
689
690 CLOSE DUTSTACUR;
691 END get_from_history_dutsta;
692
693 PROCEDURE get_from_history_payele
694 (
695 p_sr_assignment_id IN NUMBER
696 ,p_sr_report_date IN DATE
697 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
698 )
699 IS
700 l_proc varchar2(30) := 'get_from_history_payele';
701 l_scrn_ent_val_init PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
702 l_scrn_ent_val PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
703 BEGIN
704 hr_utility.set_location('Entering:'||l_proc,5);
705
706
707 l_scrn_ent_val := l_scrn_ent_val_init;
708 -- Begin Bug# 4168162
709 g_message_name := 'Fetch Element: Retirement Plan';
710 -- End Bug# 4168162
711 GHR_HISTORY_FETCH.fetch_element_entry_value(
712 p_element_name => 'Retirement Plan',
713 p_input_value_name => 'Plan',
714 p_assignment_id => p_sr_assignment_id,
715 p_date_effective => p_sr_report_date,
716 p_screen_entry_value => l_scrn_ent_val
717 );
718 p_sr_ghr_cpdf_temp.retirement_plan := l_scrn_ent_val;
719
720 l_scrn_ent_val := l_scrn_ent_val_init;
721 -- Begin Bug# 4168162
722 g_message_name := 'Fetch Element: FEGLI';
723 -- End Bug# 4168162
724 GHR_HISTORY_FETCH.fetch_element_entry_value(
725 p_element_name => 'FEGLI',
726 p_input_value_name => 'FEGLI',
727 p_assignment_id => p_sr_assignment_id,
728 p_date_effective => p_sr_report_date,
729 p_screen_entry_value => l_scrn_ent_val
730 );
731 p_sr_ghr_cpdf_temp.fegli := l_scrn_ent_val;
732
733
734 -- Start Bug 1635449
735 l_scrn_ent_val := l_scrn_ent_val_init;
736 -- Begin Bug# 4168162
737 g_message_name := 'Fetch Element: HB Pre Tax plan';
738 -- End Bug# 4168162
739 GHR_HISTORY_FETCH.fetch_element_entry_value(
740 p_element_name => 'Health Benefits Pre tax',
741 p_input_value_name => 'Health Plan',
742 p_assignment_id => p_sr_assignment_id,
743 p_date_effective => p_sr_report_date,
744 p_screen_entry_value => l_scrn_ent_val);
745 p_sr_ghr_cpdf_temp.health_plan := SUBSTR(LTRIM(l_scrn_ent_val), 1 ,2);
746 l_scrn_ent_val := l_scrn_ent_val_init;
747 -- Begin Bug# 4168162
748 g_message_name := 'Fetch Element: HB Pre tax Enrl';
749 -- End Bug# 4168162
750 GHR_HISTORY_FETCH.fetch_element_entry_value(
751 p_element_name => 'Health Benefits Pre tax',
752 p_input_value_name => 'Enrollment',
753 p_assignment_id => p_sr_assignment_id,
754 p_date_effective => p_sr_report_date,
755 p_screen_entry_value => l_scrn_ent_val
756 );
757 IF l_scrn_ent_val is NULL and p_sr_ghr_cpdf_temp.health_plan is NULL THEN
758 l_scrn_ent_val := l_scrn_ent_val_init;
759 -- Begin Bug# 4168162
760 g_message_name := ' Fetch Element: HB plan';
761 -- End Bug# 4168162
762 GHR_HISTORY_FETCH.fetch_element_entry_value(
763 p_element_name => 'Health Benefits',
764 p_input_value_name => 'Health Plan',
765 p_assignment_id => p_sr_assignment_id,
766 p_date_effective => p_sr_report_date,
767 p_screen_entry_value => l_scrn_ent_val);
768 p_sr_ghr_cpdf_temp.health_plan := SUBSTR(LTRIM(l_scrn_ent_val), 1 ,2);
769 l_scrn_ent_val := l_scrn_ent_val_init;
770 -- Begin Bug# 4168162
771 g_message_name := ' Fetch Element: HB Enrollment';
772 -- End Bug# 4168162
773 GHR_HISTORY_FETCH.fetch_element_entry_value(
774 p_element_name => 'Health Benefits',
775 p_input_value_name => 'Enrollment',
776 p_assignment_id => p_sr_assignment_id,
777 p_date_effective => p_sr_report_date,
778 p_screen_entry_value => l_scrn_ent_val
779 );
780 END IF;
781 p_sr_ghr_cpdf_temp.health_plan := NVL(p_sr_ghr_cpdf_temp.health_plan, ' ') ||
782 SUBSTR(LTRIM(l_scrn_ent_val), 1, 1);
783 -- End Bug 1635449
784 -- Changes for Payroll Integration
785 -- Name Bug Date Comments
786 -- ---- ----- ------ -----------
787 -- Madhuri Payroll Intg 04-Jul-03 Changing the Basic Salary Rate
788 -- Input Value name from 'Salary' to 'Rate'
789 --
790 l_scrn_ent_val := l_scrn_ent_val_init;
791 -- Begin Bug# 4168162
792 g_message_name := 'Fetch Element: Basic Salary';
793 -- End Bug# 4168162
794 GHR_HISTORY_FETCH.fetch_element_entry_value(
795 p_element_name => 'Basic Salary Rate',
796 p_input_value_name => 'Rate',
797 p_assignment_id => p_sr_assignment_id,
798 p_date_effective => p_sr_report_date,
799 p_screen_entry_value => l_scrn_ent_val
800 );
801 p_sr_ghr_cpdf_temp.to_basic_pay := to_number(l_scrn_ent_val);
802
803 l_scrn_ent_val := l_scrn_ent_val_init;
804
805 -- Changes for Payroll Integration
806 -- Name Bug Date Comments
807 -- ---- ----- ------ -----------
808 -- Madhuri Payroll Intg 18-AUG-03 Changing the Locality Pay
809 -- Input Value name from 'Amount' to 'Rate'
810 --
811 -- Begin Bug# 4168162
812 g_message_name := 'Fetch Element: Locality Pay';
813 -- End Bug# 4168162
814 GHR_HISTORY_FETCH.fetch_element_entry_value(
815 -- FWFA Changes Bug#4444609
816 p_element_name => 'Locality Pay or SR Supplement',
817 -- FWFA Changes Modify 'Locality Pay' to 'Locality Pay or SR Supplement'
818 p_input_value_name => 'Rate',
819 p_assignment_id => p_sr_assignment_id,
820 p_date_effective => p_sr_report_date,
821 p_screen_entry_value => l_scrn_ent_val
822 );
823 p_sr_ghr_cpdf_temp.to_locality_adj := to_number(l_scrn_ent_val);
824
825 l_scrn_ent_val := l_scrn_ent_val_init;
826 -- Begin Bug# 4168162
827 g_message_name := 'Fetch Element: Staffing Diff';
828 -- End Bug# 4168162
829 GHR_HISTORY_FETCH.fetch_element_entry_value(
830 p_element_name => 'Staffing Differential',
831 p_input_value_name => 'Amount',
832 p_assignment_id => p_sr_assignment_id,
833 p_date_effective => p_sr_report_date,
834 p_screen_entry_value => l_scrn_ent_val
835 );
836 p_sr_ghr_cpdf_temp.to_staffing_differential := to_number(l_scrn_ent_val);
837
838 l_scrn_ent_val := l_scrn_ent_val_init;
839 -- Begin Bug# 4168162
840 g_message_name := 'Fetch Element:Supervisory Diff';
841 -- End Bug# 4168162
842 GHR_HISTORY_FETCH.fetch_element_entry_value(
843 p_element_name => 'Supervisory Differential',
844 p_input_value_name => 'Amount',
845 p_assignment_id => p_sr_assignment_id,
846 p_date_effective => p_sr_report_date,
847 p_screen_entry_value => l_scrn_ent_val
848 );
849 p_sr_ghr_cpdf_temp.to_supervisory_differential := to_number(l_scrn_ent_val);
850
851 l_scrn_ent_val := l_scrn_ent_val_init;
852 -- Begin Bug# 4168162
853 g_message_name := 'Fetch Element:Retention Allow';
854 -- End Bug# 4168162
855 GHR_HISTORY_FETCH.fetch_element_entry_value(
856 p_element_name => 'Retention Allowance',
857 p_input_value_name => 'Amount',
858 p_assignment_id => p_sr_assignment_id,
859 p_date_effective => p_sr_report_date,
860 p_screen_entry_value => l_scrn_ent_val
861 );
862 p_sr_ghr_cpdf_temp.to_retention_allowance := to_number(l_scrn_ent_val);
863
864 END get_from_history_payele;
865
866 PROCEDURE calc_is_foreign_duty_station
867 ( p_report_date in date
868 )
869 IS
870 l_proc varchar2(30) := 'calc_is_foreign_duty_station';
871 CURSOR CALCDUTSTA_CUR IS
872 SELECT STATE_OR_COUNTRY_CODE
873 FROM GHR_DUTY_STATIONS_F
874 WHERE trunc(p_report_date) between effective_start_date and
875 nvl(effective_end_date, p_report_date)
876 AND DUTY_STATION_ID =
877 (SELECT LEI_INFORMATION3
878 FROM HR_LOCATION_EXTRA_INFO
879 WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
880 AND LOCATION_ID = g_location_id);
881
882 l_STATE_CNTRY_CODE GHR_DUTY_STATIONS_F.STATE_OR_COUNTRY_CODE%TYPE;
883
884 BEGIN
885 hr_utility.set_location('Entering:'||l_proc,5);
886
887
888 OPEN CALCDUTSTA_CUR;
889
890 FETCH CALCDUTSTA_CUR INTO l_STATE_CNTRY_CODE;
891
892 CLOSE CALCDUTSTA_CUR;
893
894 -- a determination of whether a duty station is foreign or domestic
895 -- has been hard-coded. at the time this proc was written, there
896 -- was talk that a future release of GHR would represent this value
897 -- as a flexfield (i.e., no need to calculate). furthermore,
898 -- the ghr_cpdf_temp.from_duty_station_code will be used as
899 -- a temporary storage for this value
900
901
902 IF (l_STATE_CNTRY_CODE >= '01' AND
903 l_STATE_CNTRY_CODE <= '99' ) OR
904 l_STATE_CNTRY_CODE IN ('GQ','RQ','AQ','FM','JQ',
905 'CQ','MQ','RM','HQ','PS',
906 'BQ','WQ','VQ')
907 THEN
908 g_ghr_cpdf_temp.from_duty_station_code := 'N';
909 ELSE
910 g_ghr_cpdf_temp.from_duty_station_code := 'Y';
911 END IF;
912
913 END calc_is_foreign_duty_station;
914
915 PROCEDURE insert_row
916 IS
917 l_proc varchar2(30) := 'insert_row';
918 -- Bug 4542476
919 l_locality_pay_area ghr_locality_pay_areas_f.locality_pay_area_code%type;
920 l_equivalent_pay_plan ghr_pay_plans.equivalent_pay_plan%type;
921 -- End Bug 4542476
922 BEGIN
923 hr_utility.set_location('Entering:'||l_proc,5);
924 -- Bug#3231946 Added reference to parameters as the function definition is changed
925
926 l_locality_pay_area := ghr_cpdf_dynrpt.get_loc_pay_area_code(p_duty_station_id => g_duty_station_id,
927 p_effective_date => g_ghr_cpdf_temp.effective_date);
928 l_equivalent_pay_plan := ghr_cpdf_dynrpt.get_equivalent_pay_plan(
929 NVL(g_ghr_cpdf_temp.retained_pay_plan, g_ghr_cpdf_temp.to_pay_plan));
930
931 IF (l_locality_pay_area = '99')
932 THEN
933 g_ghr_cpdf_temp.from_locality_adj := NULL;
934 g_ghr_cpdf_temp.to_locality_adj := NULL;
935 ELSIF l_equivalent_pay_plan = 'FW'
936 THEN
937 g_ghr_cpdf_temp.from_locality_adj := NULL;
938 g_ghr_cpdf_temp.to_locality_adj := NULL;
939 END IF;
940
941 -- Bug 4542476
942 IF g_ghr_cpdf_temp.to_locality_adj = 0 THEN
943 IF l_equivalent_pay_plan = 'GS' AND l_locality_pay_area = 'ZZ' THEN
944 g_ghr_cpdf_temp.to_locality_adj := NULL;
945 ELSIF l_equivalent_pay_plan = 'GS' AND NVL(l_locality_pay_area,'-1') <> 'ZZ' THEN
946 g_ghr_cpdf_temp.to_locality_adj := 0;
947 ELSE
948 g_ghr_cpdf_temp.to_locality_adj := NULL;
949 END IF;
950 END IF;
951 -- End Bug 4542476
952
953 INSERT INTO ghr_cpdf_temp (
954 report_type
955 ,session_id
956 ,academic_discipline
957 ,agency_code
958 ,annuitant_indicator
959 ,award_amount
960 ,bargaining_unit_status
961 ,benefit_amount
962 ,citizenship
963 ,creditable_military_service
964 ,current_appointment_auth1
965 ,current_appointment_auth2
966 ,to_duty_station_code
967 ,education_level
968 ,effective_date
969 ,employee_date_of_birth
970 ,employee_first_name
971 ,employee_last_name
972 ,employee_middle_names
973 ,from_national_identifier
974 ,fegli
975 ,fers_coverage
976 ,first_action_la_code1
977 ,first_action_la_code2
978 ,first_noa_code
979 ,flsa_category
980 ,from_basic_pay
981 ,from_duty_station_code
982 ,from_grade_or_level
983 ,from_locality_adj
984 ,from_occ_code
985 ,from_pay_table_id
986 ,from_pay_basis
987 ,from_pay_plan
988 ,from_pay_rate_determinant
989 ,from_retirement_coverage
990 ,from_step_or_rate
991 ,from_total_salary
992 ,from_work_schedule
993 ,frozen_service
994 ,functional_class
995 ,handicap_code
996 ,health_plan
997 ,individual_group_award
998 ,organizational_component
999 ,pay_status
1000 ,personnel_office_id
1001 ,position_occupied
1002 ,race_national_origin
1003 ,rating_of_record
1004 ,rating_of_record_level
1005 ,rating_of_record_pattern
1006 ,rating_of_record_period_ends
1007 ,retained_grade_or_level
1008 ,retained_pay_plan
1009 ,retained_step_or_rate
1010 ,retirement_plan
1011 ,second_noa_code
1012 ,service_comp_date
1013 ,sex
1014 ,supervisory_status
1015 ,tenure
1016 ,to_basic_pay
1017 ,to_grade_or_level
1018 ,to_locality_adj
1019 ,to_national_identifier
1020 ,to_occ_code
1021 ,to_pay_basis
1022 ,to_pay_plan
1023 ,to_pay_rate_determinant
1024 ,to_pay_table_id
1025 ,to_retention_allowance
1026 ,to_staffing_differential
1027 ,to_step_or_rate
1028 ,to_supervisory_differential
1029 ,to_total_salary
1030 ,to_work_schedule
1031 ,veterans_preference
1032 ,veterans_status
1033 ,year_degree_attained,
1034 -- employee_first_name,
1035 -- employee_middle_names,
1036 name_title,
1037 position_title,
1038 award_dollars,
1039 award_hours,
1040 award_percentage,
1041 SCD_retirement,
1042 SCD_rif,
1043 race_ethnic_info
1044 -- created_by,
1045 -- creation_Date,
1046 -- last_updated_by,
1047 -- last_update_date,
1048 -- last_update_login
1049 )
1050 values (
1051 'STATUS'
1052 ,userenv('SESSIONID')
1053 ,g_ghr_cpdf_temp.academic_discipline
1054 ,g_ghr_cpdf_temp.agency_code
1055 ,g_ghr_cpdf_temp.annuitant_indicator
1056 ,g_ghr_cpdf_temp.award_amount
1057 ,g_ghr_cpdf_temp.bargaining_unit_status
1058 ,g_ghr_cpdf_temp.benefit_amount
1059 ,g_ghr_cpdf_temp.citizenship
1060 ,g_ghr_cpdf_temp.creditable_military_service
1061 ,g_ghr_cpdf_temp.current_appointment_auth1
1062 ,g_ghr_cpdf_temp.current_appointment_auth2
1063 ,g_ghr_cpdf_temp.to_duty_station_code
1064 ,g_ghr_cpdf_temp.education_level
1065 ,g_ghr_cpdf_temp.effective_date
1066 ,g_ghr_cpdf_temp.employee_date_of_birth
1067 ,g_ghr_cpdf_temp.employee_first_name
1068 ,g_ghr_cpdf_temp.employee_last_name
1069 ,g_ghr_cpdf_temp.employee_middle_names
1070 ,g_ghr_cpdf_temp.from_national_identifier
1071 ,g_ghr_cpdf_temp.fegli
1072 ,g_ghr_cpdf_temp.fers_coverage
1073 ,g_ghr_cpdf_temp.first_action_la_code1
1074 ,g_ghr_cpdf_temp.first_action_la_code2
1075 ,g_ghr_cpdf_temp.first_noa_code
1076 ,g_ghr_cpdf_temp.flsa_category
1077 ,g_ghr_cpdf_temp.from_basic_pay
1078 ,g_ghr_cpdf_temp.from_duty_station_code
1079 ,g_ghr_cpdf_temp.from_grade_or_level
1080 ,g_ghr_cpdf_temp.from_locality_adj
1081 ,g_ghr_cpdf_temp.from_occ_code
1082 ,g_ghr_cpdf_temp.from_pay_table_id
1083 ,g_ghr_cpdf_temp.from_pay_basis
1084 ,g_ghr_cpdf_temp.from_pay_plan
1085 ,g_ghr_cpdf_temp.from_pay_rate_determinant
1086 ,g_ghr_cpdf_temp.from_retirement_coverage
1087 ,g_ghr_cpdf_temp.from_step_or_rate
1088 ,g_ghr_cpdf_temp.from_total_salary
1089 ,g_ghr_cpdf_temp.from_work_schedule
1090 ,g_ghr_cpdf_temp.frozen_service
1091 ,g_ghr_cpdf_temp.functional_class
1092 ,g_ghr_cpdf_temp.handicap_code
1093 ,g_ghr_cpdf_temp.health_plan
1094 ,g_ghr_cpdf_temp.individual_group_award
1095 ,g_ghr_cpdf_temp.organizational_component
1096 ,g_ghr_cpdf_temp.pay_status
1097 ,g_ghr_cpdf_temp.personnel_office_id
1098 ,g_ghr_cpdf_temp.position_occupied
1099 ,g_ghr_cpdf_temp.race_national_origin
1100 ,g_ghr_cpdf_temp.rating_of_record
1101 ,g_ghr_cpdf_temp.rating_of_record_level
1102 ,g_ghr_cpdf_temp.rating_of_record_pattern
1103 ,g_ghr_cpdf_temp.rating_of_record_period_ends
1104 ,g_ghr_cpdf_temp.retained_grade_or_level
1105 ,g_ghr_cpdf_temp.retained_pay_plan
1106 ,g_ghr_cpdf_temp.retained_step_or_rate
1107 ,g_ghr_cpdf_temp.retirement_plan
1108 ,g_ghr_cpdf_temp.second_noa_code
1109 ,g_ghr_cpdf_temp.service_comp_date
1110 ,g_ghr_cpdf_temp.sex
1111 ,g_ghr_cpdf_temp.supervisory_status
1112 ,g_ghr_cpdf_temp.tenure
1113 ,g_ghr_cpdf_temp.to_basic_pay
1114 ,g_ghr_cpdf_temp.to_grade_or_level
1115 ,g_ghr_cpdf_temp.to_locality_adj
1116 ,g_ghr_cpdf_temp.to_national_identifier
1117 ,g_ghr_cpdf_temp.to_occ_code
1118 ,g_ghr_cpdf_temp.to_pay_basis
1119 ,g_ghr_cpdf_temp.to_pay_plan
1120 ,g_ghr_cpdf_temp.to_pay_rate_determinant
1121 ,g_ghr_cpdf_temp.to_pay_table_id
1122 ,g_ghr_cpdf_temp.to_retention_allowance
1123 ,g_ghr_cpdf_temp.to_staffing_differential
1124 ,g_ghr_cpdf_temp.to_step_or_rate
1125 ,g_ghr_cpdf_temp.to_supervisory_differential
1126 ,g_ghr_cpdf_temp.to_total_salary
1127 ,g_ghr_cpdf_temp.to_work_schedule
1128 ,g_ghr_cpdf_temp.veterans_preference
1129 ,g_ghr_cpdf_temp.veterans_status
1130 ,g_ghr_cpdf_temp.year_degree_attained,
1131 -- p_ghr_cpdf_temp_rec.employee_first_name,
1132 -- p_ghr_cpdf_temp_rec.employee_middle_names,
1133 g_ghr_cpdf_temp.name_title,
1134 g_ghr_cpdf_temp.position_title,
1135 g_ghr_cpdf_temp.award_dollars,
1136 g_ghr_cpdf_temp.award_hours,
1137 g_ghr_cpdf_temp.award_percentage,
1138 g_ghr_cpdf_temp.SCD_retirement,
1139 g_ghr_cpdf_temp.SCD_rif,
1140 g_ghr_cpdf_temp.race_ethnic_info
1141 -- 1,sysdate,1,sysdate,1
1142 );
1143 END insert_row;
1144
1145 PROCEDURE purge_suppression
1146 IS
1147 l_proc varchar2(30) := 'purge_suppression';
1148 BEGIN
1149 hr_utility.set_location('Entering:'||l_proc,5);
1150
1151 -- bug 743502 moved the checking of agency code matching the parameter passed in to
1152 -- to as soon as possible, not here at the end!
1153 DELETE FROM ghr_cpdf_temp
1154 WHERE (report_type='STATUS')
1155 AND (
1156 -- *** SUPPRESS NON APPROPRIATED EMPLOYEES / COMMISSIONED OFFICERS
1157 ( to_pay_plan IN ('NA','NL','NS','CC') )
1158 -- *** EXCLUDE NON US CITIZENS WORKING IN FOREIGN DUTY STATIONS
1159 OR ( from_duty_station_code = 'Y'
1160 AND decode(citizenship, NULL, ' ', citizenship) <> '1' )
1161 -- *** EXCLUDE CERTAIN AGENCIES
1162 OR ( agency_code IN ('CI00','DD05','DD28','FR00',
1163 'PO00','PJ00','TV00','WH01') )
1164 -- *** EXCLUDE CERTAIN SUBELEMENTS
1165 OR ( substr(agency_code,1,2) IN ('LL','LB','LA','LD','LG','LC') )
1166 -- *** EXCLUDE NON SELECTED AGENCY CODE
1167 -- OR ( decode(agency_code, NULL, ' ', agency_code)
1168 -- not like DECODE(g_agency,NULL,'%',rtrim(g_agency)||'%'))
1169 )
1170 ;
1171
1172 END purge_suppression;
1173
1174 PROCEDURE get_suffix_lname(p_last_name in varchar2,
1175 p_report_date in date,
1176 p_suffix out nocopy varchar2,
1177 p_lname out nocopy varchar2)
1178 IS
1179 l_suffix_pos number;
1180 l_total_len number;
1181 l_proc varchar2(30) := 'get_suffix_lname';
1182
1183 CURSOR GET_SUFFIX IS
1184 SELECT INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
1185 LENGTH(p_last_name)
1186 FROM HR_LOOKUPS
1187 WHERE LOOKUP_TYPE = 'GHR_US_NAME_SUFFIX'
1188 AND TRUNC(p_report_date) BETWEEN NVL(START_DATE_ACTIVE,p_report_date)
1189 AND NVL(END_DATE_ACTIVE,p_report_date)
1190 AND RTRIM(SUBSTR(TRANSLATE(UPPER(p_last_name),',.',' '),
1191 INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
1192 LENGTH(p_last_name)),' ') = ' '||UPPER(LOOKUP_CODE)
1193 AND ROWNUM = 1;
1194 BEGIN
1195
1196 hr_utility.set_location('Entering:'||l_proc,5);
1197
1198 IF GET_SUFFIX%ISOPEN THEN
1199 CLOSE GET_SUFFIX;
1200 END IF;
1201
1202 OPEN GET_SUFFIX;
1203 --getting the position of a suffix appended in the lastname by comparing the lastname
1204 -- with the suffixes available in lookup*/
1205 FETCH GET_SUFFIX INTO l_suffix_pos, l_total_len;
1206 -- if suffix not found then returning lastname otherwise returning lastname without suffix
1207 -- and suffix
1208 IF GET_SUFFIX%NOTFOUND THEN
1209 p_lname := RTRIM(p_last_name,' ,.');
1210 p_suffix := NULL;
1211 ELSE
1212 p_lname := RTRIM(SUBSTR(p_last_name, 0, l_suffix_pos-1),' ,.');
1213 p_suffix := SUBSTR(p_last_name,l_suffix_pos+1,l_total_len);
1214 END IF;
1215 CLOSE GET_SUFFIX;
1216 END get_suffix_lname;
1217
1218 PROCEDURE populate_ghr_cpdf_temp (p_agency IN VARCHAR2,
1219 p_report_date IN DATE)
1220 IS
1221 l_proc varchar2(30) := 'populate_ghr_cpdf_temp';
1222 CURSOR assignments_f_cur is
1223 SELECT asg.assignment_id,
1224 asg.person_id,
1225 asg.position_id,
1226 asg.grade_id,
1227 asg.job_id,
1228 asg.location_id,
1229 asg.effective_start_date,
1230 asg.business_group_id,
1231 ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) agency_code
1232 FROM PER_ASSIGNMENTS_F asg
1233 WHERE
1234 -- only consider "Active" assignments as defined by below, also only look at
1235 -- assignments that are assigned to a valid person as of the report date.
1236 p_report_date between asg.effective_start_date and asg.effective_end_date
1237 AND asg.assignment_status_type_id in
1238 (select ast.assignment_status_type_id
1239 from PER_ASSIGNMENT_STATUS_TYPES ast
1240 where ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
1241 AND asg.assignment_type <> 'B'
1242 AND asg.position_id IS NOT NULL
1243 AND ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) like p_agency
1244 ORDER BY assignment_id;
1245 -- Bug 3704123 - Adding order by clause for the above statement so that results will be in temp segment
1246
1247 l_assignments_f_rec assignments_f_cur%ROWTYPE;
1248 --- 3671043 Bug fix
1249 l_log_text ghr_process_log.log_text%type;
1250 l_message_name ghr_process_log.message_name%type;
1251 l_log_date ghr_process_log.log_date%type;
1252 l_suffix per_all_people_f.title%type;
1253 l_last_name per_all_people_f.last_name%type;
1254
1255
1256 CURSOR cur_per_details(p_person_id per_people_f.person_id%type)
1257 IS
1258 SELECT full_name name ,national_identifier ssn,last_name,first_name,middle_names, title
1259 FROM per_all_people_f
1260 WHERE person_id=p_person_id;
1261
1262
1263 l_business_group_id per_assignments_f.business_group_id%type;
1264 l_full_name per_people_f.full_name%type;
1265 l_ssn per_people_f.national_identifier%type;
1266 l_records_found BOOLEAN;
1267 l_mesgbuff1 VARCHAR2(4000);
1268 ll_per_ei_data per_people_extra_info%rowtype;
1269 -- FWFA Changes Declare variable l_calc_pay_table_id
1270 Cursor c_pay_table_name (p_user_table_id number) is
1271 SELECT SUBSTR(user_table_name,1,4) user_table_name
1272 FROM pay_user_tables
1273 WHERE user_table_id = p_user_table_id;
1274 l_calc_pay_table_id VARCHAR2(4);
1275 -- This function returns true if the Pay Plan passed in is an 'GS' equivalent
1276 FUNCTION pp_gs_equivalent (p_pay_plan IN VARCHAR2)
1277 RETURN BOOLEAN IS
1278 CURSOR cur_ppl IS
1279 SELECT 1
1280 FROM ghr_pay_plans ppl
1281 WHERE ppl.pay_plan = p_pay_plan
1282 AND ppl.equivalent_pay_plan = 'GS';
1283 --
1284 BEGIN
1285 FOR cur_ppl_rec IN cur_ppl LOOP
1286 RETURN(TRUE);
1287 END LOOP;
1288 --
1289 RETURN(FALSE);
1290 END pp_gs_equivalent;
1291
1292 BEGIN
1293
1294 hr_utility.set_location('Entering:'||l_proc,5);
1295 ghr_mto_int.set_log_program_name('GHR_CPDF_STATRPT');
1296 g_report_date := p_report_date;
1297 g_agency := p_agency;
1298 l_records_found:=FALSE;
1299 -- cleanup_table;
1300 initialize_record;
1301
1302 FOR l_assignments_f_rec IN assignments_f_cur
1303 LOOP
1304 BEGIN
1305 -- initialize every iteration
1306 initialize_record;
1307 -- assign globals
1308 g_assignment_id := l_assignments_f_rec.assignment_id;
1309 g_person_id := l_assignments_f_rec.person_id;
1310 g_position_id := l_assignments_f_rec.position_id;
1311 g_grade_id := l_assignments_f_rec.grade_id;
1312 g_job_id := l_assignments_f_rec.job_id;
1313 g_location_id := l_assignments_f_rec.location_id;
1314 g_ghr_cpdf_temp.agency_code := l_assignments_f_rec.agency_code;
1315 -- added for EHRI reports
1316 l_business_group_id := l_assignments_f_rec.business_group_id;
1317 -- added for EHRI reports
1318
1319 -- Bug 714944 -- No not report on NAF positions:
1320 IF ghr_cpdf_dynrpt.exclude_position (p_position_id => g_position_id
1321 ,p_effective_date => p_report_date) THEN
1322 GOTO end_asg_loop; -- loop for the next one!
1323 END IF;
1324 --
1325 --
1326 BEGIN
1327
1328 FOR per_det in cur_per_details(g_person_id)
1329 LOOP
1330 -- Bug# 4648811 extracting suffix from the lastname
1331 get_suffix_lname(per_det.last_name,
1332 p_report_date,
1333 l_suffix,
1334 l_last_name);
1335 g_ghr_cpdf_temp.employee_last_name := l_last_name;
1336 g_ghr_cpdf_temp.employee_first_name := per_det.first_name;
1337 g_ghr_cpdf_temp.employee_middle_names := per_det.middle_names;
1338 g_ghr_cpdf_temp.name_title := l_suffix;
1339 --End Bug# 4648811
1340 END LOOP;
1341
1342 -- Begin Bug# 4168162
1343 g_message_name := 'Fetch Position title';
1344 --l_message_name :='get_position_title_pos';
1345 -- End Bug# 4168162
1346
1347 g_ghr_cpdf_temp.position_title := ghr_api.get_position_title_pos(
1348 p_position_id => g_position_id,
1349 p_business_group_id => l_business_group_id,
1350 p_effective_date => g_report_date);
1351
1352
1353 /* ghr_history_fetch.fetch_peopleei
1354 (p_person_id => g_person_id,
1355 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
1356 p_date_effective => nvl(g_report_date,trunc(sysdate)),
1357 p_per_ei_data => ll_per_ei_data
1358 );
1359
1360 g_ghr_cpdf_temp.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
1361 g_ghr_cpdf_temp.SCD_retirement:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information7);
1362 */
1363 --
1364 -- Bug 3671043 Handling Exceptions (madhuri)
1365 -- Begin Bug# 4168162
1366 g_message_name := 'Fetch Appointment date';
1367 --l_message_name := 'get_appointment_date';
1368 -- End Bug# 4168162
1369 get_appointment_date(p_person_id => g_person_id
1370 ,p_report_date => p_report_date
1371 ,p_appointment_date => g_appointment_date);
1372 --
1373 -- call fetch routines to populate record
1374 -- Begin Bug# 4168162
1375 --l_message_name := 'get_from_history_asgnei';
1376 -- End Bug# 4168162
1377 get_from_history_asgnei
1378 (
1379 p_sr_assignment_id => g_assignment_id
1380 ,p_sr_report_date => g_report_date
1381 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1382 );
1383 -- FWFA Change Get pay table id
1384 -- Bug#5063289 Fetch the First 4 characters of Pay table name.
1385 FOR pay_table_rec IN c_pay_table_name(g_ghr_cpdf_temp.to_pay_table_id)
1386 LOOP
1387 l_calc_pay_table_id := pay_table_rec.user_table_name;
1388 END LOOP;
1389
1390 -- Begin Bug# 4168162
1391 --l_message_name := 'get_from_history_payele';
1392 -- End Bug# 4168162
1393 get_from_history_payele
1394 (
1395 p_sr_assignment_id => g_assignment_id
1396 ,p_sr_report_date => g_report_date
1397 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1398 );
1399
1400 -- Begin Bug# 4168162
1401 g_message_name := 'Fetch Person Details';
1402 --l_message_name := 'get_from_history_people';
1403 -- End Bug# 4168162
1404 get_from_history_people
1405 (
1406 p_sr_person_id => g_person_id
1407 ,p_sr_report_date => g_report_date
1408 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1409 ); -- g_ghr_cpdf_temp.to_national_identifier
1410
1411 -- Begin Bug# 4168162
1412 --l_message_name := 'get_from_history_ancrit';
1413 -- End Bug# 4168162
1414 get_from_history_ancrit
1415 (
1416 p_sr_person_id => g_person_id
1417 ,p_sr_report_date => g_report_date
1418 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1419 );
1420
1421 -- Begin Bug# 4168162
1422 --l_message_name := 'get_from_history_peopei';
1423 -- End Bug# 4168162
1424 get_from_history_peopei
1425 (
1426 p_sr_person_id => g_person_id
1427 ,p_sr_report_date => g_report_date
1428 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1429 );
1430
1431 IF g_position_id IS NOT NULL
1432 THEN
1433 -- Begin Bug# 4168162
1434 --l_message_name := 'get_from_history_posiei';
1435 -- End Bug# 4168162
1436 get_from_history_posiei
1437 (
1438 p_sr_position_id => g_position_id
1439 ,p_sr_report_date => g_report_date
1440 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1441 );
1442 END IF;
1443
1444
1445 IF g_grade_id IS NOT NULL
1446 THEN
1447 -- Begin Bug# 4168162
1448 g_message_name := 'Fetch Grade Details';
1449 --l_message_name := 'get_from_history_gradef';
1450 -- End Bug# 4168162
1451 get_from_history_gradef
1452 (
1453 p_sr_grade_id => g_grade_id
1454 ,p_sr_report_date => g_report_date
1455 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1456 );
1457 END IF;
1458
1459 IF g_job_id IS NOT NULL
1460 THEN
1461 -- Begin Bug# 4168162
1462 g_message_name := 'Fetch Job Details';
1463 --l_message_name := 'get_from_history_jobdef';
1464 -- End Bug# 4168162
1465 get_from_history_jobdef
1466 (
1467 p_sr_job_id => g_job_id
1468 ,p_sr_report_date => g_report_date
1469 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1470 );
1471 END IF;
1472
1473 IF g_location_id IS NOT NULL
1474 THEN
1475 -- Begin Bug# 4168162
1476 -- l_message_name := 'get_from_history_dutsta';
1477 -- End Bug# 4168162
1478 get_from_history_dutsta
1479 (
1480 p_sr_location_id => g_location_id
1481 ,p_sr_report_date => g_report_date
1482 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
1483 );
1484 END IF;
1485
1486
1487 -- If Ethnicity is reported, RNO should be null
1488 IF g_ghr_cpdf_temp.race_ethnic_info IS NOT NULL THEN
1489 g_ghr_cpdf_temp.race_national_origin := NULL;
1490 END IF;
1491
1492 -- FWFA Change Override pay table id with that retrieved from Assignment
1493
1494 IF l_calc_pay_table_id IS NOT NULL THEN
1495 IF pp_gs_equivalent(g_ghr_cpdf_temp.to_pay_plan) AND
1496 g_ghr_cpdf_temp.to_pay_rate_determinant IN ('5','6','E','F') THEN
1497 g_ghr_cpdf_temp.to_pay_table_id := l_calc_pay_table_id;
1498 ELSE
1499 g_ghr_cpdf_temp.to_pay_table_id := NULL;
1500 END IF;
1501 ELSE
1502 IF g_ghr_cpdf_temp.to_pay_rate_determinant IN ('5','6') AND
1503 pp_gs_equivalent(g_ghr_cpdf_temp.to_pay_plan) THEN
1504 FOR pay_table_rec IN c_pay_table_name(g_ghr_cpdf_temp.to_pay_table_id)
1505 LOOP
1506 g_ghr_cpdf_temp.to_pay_table_id := pay_table_rec.user_table_name;
1507 END LOOP;
1508 ELSIF g_ghr_cpdf_temp.to_pay_rate_determinant in ('E','F') AND
1509 pp_gs_equivalent(g_ghr_cpdf_temp.retained_pay_plan) THEN
1510 FOR pay_table_rec IN c_pay_table_name(g_retained_pay_table_id)
1511 LOOP
1512 g_ghr_cpdf_temp.to_pay_table_id := pay_table_rec.user_table_name;
1513 END LOOP;
1514 ELSE
1515 g_ghr_cpdf_temp.to_pay_table_id := NULL;
1516 END IF;
1517 END IF;
1518
1519 g_message_name := NULL;
1520
1521 l_records_found:=TRUE;
1522
1523 EXCEPTION
1524 WHEN ghr_pay_calc.pay_calc_message THEN
1525 FOR per_details in cur_per_details(g_person_id)
1526 LOOP
1527 g_message_name := 'Person EIT - Retained_grade'; --Bug# 4168162
1528 l_full_name := per_details.name;
1529 l_ssn := per_details.ssn;
1530 l_log_text := 'Error in fetching data for Employee : ' ||l_full_name||
1531 ' SSN : '||l_ssn||
1532 '; ** Error Message ** : Retained Grade details not available as on the report date' ;
1533 END LOOP;
1534 Raise CPDF_STATRPT_ERROR;
1535
1536 WHEN OTHERS THEN
1537 FOR per_details in cur_per_details(g_person_id)
1538 LOOP
1539 l_full_name := per_details.name;
1540 l_ssn := per_details.ssn;
1541 l_log_text := 'Error in fetching data for Employee : ' ||l_full_name||
1542 ' SSN : '||l_ssn||
1543 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1544 END LOOP;
1545 Raise CPDF_STATRPT_ERROR;
1546
1547
1548 END;
1549 --
1550 -- END of handling exceptions for Bug 3671043
1551 --
1552 calc_is_foreign_duty_station(p_report_date => g_report_date);
1553
1554 insert_row;
1555
1556 <<end_asg_loop>>
1557 NULL;
1558
1559 EXCEPTION
1560 WHEN CPDF_STATRPT_ERROR THEN
1561 hr_utility.set_location('Inside CPDF_STATRPT_ERROR exception ',30);
1562 ghr_mto_int.log_message(p_procedure => g_message_name, --Bug# 4168162
1563 p_message => l_log_text
1564 );
1565 COMMIT;
1566 END;
1567 END LOOP;
1568
1569 IF NOT l_records_found THEN
1570 g_message_name:='RECORDS_NOT_FOUND'; --Bug# 4168162
1571 l_log_text:= 'No Records found for the given Report Date '||g_report_date;
1572 ghr_mto_int.log_message(p_procedure => g_message_name, --Bug# 4168162
1573 p_message => l_log_text
1574 );
1575
1576 l_mesgbuff1:='No Records found for the given Report Date '||g_report_date;
1577 fnd_file.put(fnd_file.log,l_mesgbuff1);
1578 fnd_file.new_line(fnd_file.log);
1579 END IF;
1580
1581
1582 -- purge per design doc
1583 purge_suppression;
1584
1585 END populate_ghr_cpdf_temp;
1586
1587 END ghr_cpdf_statrpt;