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