1 PACKAGE BODY ghr_cpdf_dynrpt AS
2 /* $Header: ghrcpdfd.pkb 120.27 2011/04/20 10:40:15 vmididho ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := 'ghr_cpdf_dynrpt.'; -- Global package name
9 --
10
11 --
12
13 ---------------------------------------------------------------------------------------------
14 -- This will delete all the dynamics report records for the current session and report type
15 -- 'DYNAMICS'
16 ---------------------------------------------------------------------------------------------
17 PROCEDURE cleanup_table IS
18 BEGIN
19 DELETE FROM ghr_cpdf_temp
20 WHERE report_type ='DYNAMICS'
21 AND session_id = USERENV('SESSIONID');
22 COMMIT;
23 END;
24 --
25 ---------------------------------------------------------------------------------------------
26 -- This function returns TRUE if we should exclude the agency
27 -- since this function is to be used in a SQL where clause can not return a BOOLEAN
28 -- so return the same thing in a VARCHAR2 field!
29 -- this list come
30 ---------------------------------------------------------------------------------------------
31 --
32 FUNCTION exclude_agency (p_agency_code IN VARCHAR2)
33 RETURN VARCHAR2 IS
34 BEGIN
35 IF p_agency_code IN ('AMAD','ARCD','DD05','DD11','DD28','DD36','DD54','DD59')
36 OR SUBSTR(p_agency_code,1,2) IN ('BJ','CI','FR','JL','LA','LB','LC','LD','LG'
37 ,'LL','LQ','OV','PI','PJ','PO','TV','WH','ZG') THEN
38 RETURN ('TRUE');
39 ELSE
40 RETURN ('FALSE');
41 END IF;
42
43 END exclude_agency;
44 --
45 ---------------------------------------------------------------------------------------------
46 -- This function returns 'TRUE' if we should exclude the noac
47 -- since this function is to be used in a SQL where clause can not return a BOOLEAN
48 -- so return the same thing in a VARCHAR2 field!
49 -- The Noac's to exclude are 900-999
50 -- Note: we include the 4 character noa's though
51 -- i.e. 100 include
52 -- 900 exclude
53 -- A100 include
54 -- A900 exclude
55 -- assumes there are no dual actions with 900 in one NOA and a NOA we report in the other!!!
56 --For title 38 changes, NOACs 850 and 855 are to be exclueded.
57 ---------------------------------------------------------------------------------------------
58 --
59 FUNCTION exclude_noac (p_first_noac IN VARCHAR2
60 ,p_second_noac IN VARCHAR2
61 ,p_noa_family_code IN VARCHAR2)
62 RETURN VARCHAR2 IS
63 l_noac VARCHAR2(4);
64 BEGIN
65 IF p_noa_family_code IN ('CORRECT','CANCEL') THEN
66 l_noac := format_noac(p_second_noac);
67 ELSE
68 l_noac := format_noac(p_first_noac);
69 END IF;
70 --
71 -- The NOACs not to be printed as per OPM standards are added to the list
72 -- These NOACs are added under due to reference of NOACs as 7% and 8% under EHRI code changes.
73 --
74 IF (l_noac BETWEEN '900' and '999') or
75 (l_noac IN ( '850','855','750','782','800','805','806','880','881','882','883')) THEN
76 RETURN ('TRUE');
77 ELSE
78 RETURN ('FALSE');
79 END IF;
80
81 END exclude_noac;
82 --
83 ---------------------------------------------------------------------------------------------
84 -- This function returns TRUE if the info passed in means it is a non us citizen
85 -- AND a foreign DS
86 ---------------------------------------------------------------------------------------------
87 FUNCTION non_us_citizen_and_foreign_ds (p_citizenship IN VARCHAR2
88 ,p_duty_station_code IN VARCHAR2)
89 RETURN BOOLEAN IS
90 l_ds_2chars VARCHAR2(2);
91 BEGIN
92 -- The definition of non us citizen is citizenship does not equal 1
93 IF p_citizenship <> 1 THEN
94 -- The deifnition of a 'foreign' duty staion is:
95 -- If the first 2 positions of the duty station are alphabetic this means it is either
96 -- 1) foreign country
97 -- or 2) US possesion
98 -- or 3) US administritive area
99 -- Since all we what is 1)'s we exclude 2) and 3)'s by the list GQ,RQ etc...
100 l_ds_2chars := TRANSLATE(SUBSTR(p_duty_station_code,1,2),'0123456789','000000000');
101 IF l_ds_2chars <> '00'
102 AND l_ds_2chars NOT IN ('GQ','RQ','AQ','FM','JQ',
103 'CQ','MQ','RM','HQ','PS',
104 'BQ','WQ','VQ') THEN
105 RETURN(TRUE);
106 END IF;
107 END IF;
108 RETURN (FALSE);
109 END non_us_citizen_and_foreign_ds;
110 --
111 ---------------------------------------------------------------------------------------------
112 -- This function returns 'TRUE' if the for the position passed in on the given date
113 -- it is not an Appopriated fund
114 ---------------------------------------------------------------------------------------------
115 FUNCTION exclude_position (p_position_id IN NUMBER
116 ,p_effective_date IN DATE)
117 RETURN BOOLEAN IS
118 --
119 l_pos_ei_grp2_data per_position_extra_info%rowtype;
120 l_position_type VARCHAR2(150);
121 --
122 BEGIN
123 -- first get the Position Type on Position Group 2
124 ghr_history_fetch.fetch_positionei(
125 p_position_id => p_position_id,
126 p_information_type => 'GHR_US_POS_GRP2',
127 p_date_effective => p_effective_date,
128 p_pos_ei_data => l_pos_ei_grp2_data);
129 --
130 l_position_type := l_pos_ei_grp2_data.poei_information17;
131 --
132 --8886374 removed APPR in NVL comparison and made '@#'
133 IF NVL(l_position_type,'@#') <> 'APPR' THEN
134 RETURN (TRUE);
135 ELSE
136 RETURN (FALSE);
137 END IF;
138 END exclude_position;
139 --
140 ---------------------------------------------------------------------------------------------
141 -- B) This section includes any getting of extra data procedures
142 ---------------------------------------------------------------------------------------------
143 --
144 ---------------------------------------------------------------------------------------------
145 -- This one gets org component (also refered to as org structure id) for a given position
146 ---------------------------------------------------------------------------------------------
147 PROCEDURE get_org_comp (p_position_id IN NUMBER
148 ,p_effective_date IN DATE
149 ,p_org_comp OUT NOCOPY VARCHAR2) IS
150 --
151 l_pos_ei_grp1_data per_position_extra_info%ROWTYPE;
152 BEGIN
153 ghr_history_fetch.fetch_positionei(p_position_id => p_position_id
154 ,p_information_type => 'GHR_US_POS_GRP1'
155 ,p_date_effective => p_effective_date
156 ,p_pos_ei_data => l_pos_ei_grp1_data);
157
158 p_org_comp := l_pos_ei_grp1_data.poei_information5;
159 EXCEPTION
160 WHEN OTHERS THEN
161 p_org_comp := NULL;
162 raise;
163 END get_org_comp;
164 --
165 ---------------------------------------------------------------------------------------------
166 -- This one gets sex for a given person
167 ---------------------------------------------------------------------------------------------
168 PROCEDURE get_sex (p_person_id IN NUMBER
169 ,p_effective_date IN DATE
170 ,p_sex OUT NOCOPY VARCHAR2) IS
171 CURSOR cur_per_sex IS
172 SELECT per.sex
173 FROM per_all_people_f per
174 WHERE per.person_id = p_person_id
175 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date;
176 BEGIN
177 FOR cur_per_sex_rec IN cur_per_sex LOOP
178 p_sex := cur_per_sex_rec.sex;
179 END LOOP;
180 EXCEPTION
181 WHEN OTHERS THEN
182 p_sex := NULL;
183 raise;
184 END get_sex;
185 --
186 ---------------------------------------------------------------------------------------------
187 -- Returns the whole ghr_pa_request_extra_info (rei) record for a given info type
188 ---------------------------------------------------------------------------------------------
189 PROCEDURE get_PAR_EI (p_pa_request_id IN NUMBER
190 ,p_noa_family_code IN VARCHAR2
191 ,p_information_type IN VARCHAR2
192 ,p_rei_rec OUT NOCOPY ghr_pa_request_extra_info%ROWTYPE) IS
193 CURSOR c_par IS
194 SELECT par.pa_request_id
195 FROM ghr_pa_requests par
196 CONNECT BY par.pa_request_id = prior par.altered_pa_request_id
197 START WITH par.pa_request_id = p_pa_request_id;
198 --
199 CURSOR cur_rei (cp_pa_request_id IN NUMBER) IS
200 SELECT *
201 FROM ghr_pa_request_extra_info rei
202 WHERE rei.information_type = p_information_type
203 AND rei.pa_request_id = cp_pa_request_id;
204 --
205 l_rei_rec ghr_pa_request_extra_info%ROWTYPE;
206 BEGIN
207 -- This extra info is actually on all NOAC's
208 -- For corrections, need a different way to get the data form the original 52 being
209 -- corrected since we do not populate the data for a correction!
210 IF p_noa_family_code <> 'CORRECT' THEN
211 FOR cur_rei_rec IN cur_rei(p_pa_request_id) LOOP
212 p_rei_rec := cur_rei_rec;
213 END LOOP;
214 ELSE
215 -- loop round all the pa_requests, picking up anything that is blank
216 FOR c_par_rec IN c_par LOOP
217 FOR cur_rei_rec IN cur_rei(c_par_rec.pa_request_id) LOOP
218 IF l_rei_rec.rei_information1 IS NULL THEN
219 l_rei_rec.rei_information1 := cur_rei_rec.rei_information1;
220 END IF;
221 IF l_rei_rec.rei_information2 IS NULL THEN
222 l_rei_rec.rei_information2 := cur_rei_rec.rei_information2;
223 END IF;
224 IF l_rei_rec.rei_information3 IS NULL THEN
225 l_rei_rec.rei_information3 := cur_rei_rec.rei_information3;
226 END IF;
227 IF l_rei_rec.rei_information4 IS NULL THEN
228 l_rei_rec.rei_information4 := cur_rei_rec.rei_information4;
229 END IF;
230 IF l_rei_rec.rei_information5 IS NULL THEN
231 l_rei_rec.rei_information5 := cur_rei_rec.rei_information5;
232 END IF;
233 IF l_rei_rec.rei_information6 IS NULL THEN
234 l_rei_rec.rei_information6 := cur_rei_rec.rei_information6;
235 END IF;
236 IF l_rei_rec.rei_information7 IS NULL THEN
237 l_rei_rec.rei_information7 := cur_rei_rec.rei_information7;
238 END IF;
239 IF l_rei_rec.rei_information8 IS NULL THEN
240 l_rei_rec.rei_information8 := cur_rei_rec.rei_information8;
241 END IF;
242 IF l_rei_rec.rei_information9 IS NULL THEN
243 l_rei_rec.rei_information9 := cur_rei_rec.rei_information9;
244 END IF;
245 IF l_rei_rec.rei_information10 IS NULL THEN
246 l_rei_rec.rei_information10 := cur_rei_rec.rei_information10;
247 END IF;
248 IF l_rei_rec.rei_information11 IS NULL THEN
249 l_rei_rec.rei_information11 := cur_rei_rec.rei_information11;
250 END IF;
251 IF l_rei_rec.rei_information12 IS NULL THEN
252 l_rei_rec.rei_information12 := cur_rei_rec.rei_information12;
253 END IF;
254 IF l_rei_rec.rei_information13 IS NULL THEN
255 l_rei_rec.rei_information13 := cur_rei_rec.rei_information13;
256 END IF;
257 IF l_rei_rec.rei_information14 IS NULL THEN
258 l_rei_rec.rei_information14 := cur_rei_rec.rei_information14;
259 END IF;
260 IF l_rei_rec.rei_information15 IS NULL THEN
261 l_rei_rec.rei_information15 := cur_rei_rec.rei_information15;
262 END IF;
263 IF l_rei_rec.rei_information16 IS NULL THEN
264 l_rei_rec.rei_information16 := cur_rei_rec.rei_information16;
265 END IF;
266 IF l_rei_rec.rei_information17 IS NULL THEN
267 l_rei_rec.rei_information17 := cur_rei_rec.rei_information17;
268 END IF;
269 IF l_rei_rec.rei_information18 IS NULL THEN
270 l_rei_rec.rei_information18 := cur_rei_rec.rei_information18;
271 END IF;
272 IF l_rei_rec.rei_information19 IS NULL THEN
273 l_rei_rec.rei_information19 := cur_rei_rec.rei_information19;
274 END IF;
275 IF l_rei_rec.rei_information20 IS NULL THEN
276 l_rei_rec.rei_information20 := cur_rei_rec.rei_information20;
277 END IF;
278 IF l_rei_rec.rei_information21 IS NULL THEN
279 l_rei_rec.rei_information21 := cur_rei_rec.rei_information21;
280 END IF;
281 IF l_rei_rec.rei_information22 IS NULL THEN
282 l_rei_rec.rei_information22 := cur_rei_rec.rei_information22;
283 END IF;
284 IF l_rei_rec.rei_information23 IS NULL THEN
285 l_rei_rec.rei_information23 := cur_rei_rec.rei_information23;
286 END IF;
287 IF l_rei_rec.rei_information24 IS NULL THEN
288 l_rei_rec.rei_information24 := cur_rei_rec.rei_information24;
289 END IF;
290 IF l_rei_rec.rei_information25 IS NULL THEN
291 l_rei_rec.rei_information25 := cur_rei_rec.rei_information25;
292 END IF;
293 IF l_rei_rec.rei_information26 IS NULL THEN
294 l_rei_rec.rei_information26 := cur_rei_rec.rei_information26;
295 END IF;
296 IF l_rei_rec.rei_information27 IS NULL THEN
297 l_rei_rec.rei_information27 := cur_rei_rec.rei_information27;
298 END IF;
299 IF l_rei_rec.rei_information28 IS NULL THEN
300 l_rei_rec.rei_information28 := cur_rei_rec.rei_information28;
301 END IF;
302 IF l_rei_rec.rei_information29 IS NULL THEN
303 l_rei_rec.rei_information29 := cur_rei_rec.rei_information29;
304 END IF;
305 IF l_rei_rec.rei_information30 IS NULL THEN
306 l_rei_rec.rei_information30 := cur_rei_rec.rei_information30;
307 END IF;
308 END LOOP;
309 END LOOP;
310 p_rei_rec := l_rei_rec;
311 END IF;
312 EXCEPTION
313 WHEN OTHERS THEN
314 p_rei_rec := NULL;
315 raise;
316 END get_PAR_EI;
317 --
318 ---------------------------------------------------------------------------------------------
319 -- This one gets the details from the PAR Extra info for Performance appraisal
320 ---------------------------------------------------------------------------------------------
321 PROCEDURE get_per_sit_perf_appraisal(p_person_id IN NUMBER
322 ,p_effective_date IN DATE
323 ,p_rating_of_record_level OUT NOCOPY VARCHAR2
324 ,p_rating_of_record_pattern OUT NOCOPY VARCHAR2
325 ,p_rating_of_record_period OUT NOCOPY DATE) IS
326 --
327 l_special_info ghr_api.special_information_type;
328 l_emp_number per_people_f.employee_number%TYPE;
329 CURSOR c_per IS
330 SELECT per.employee_number
331 FROM per_people_f per
332 WHERE per.person_id = p_person_id
333 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
334 AND per.effective_end_date;
335 BEGIN
336 ghr_api.return_special_information(p_person_id, 'US Fed Perf Appraisal',
337 p_effective_date, l_special_info);
338
339 IF l_special_info.object_version_number IS NOT NULL THEN
340 p_rating_of_record_level := l_special_info.segment5;
341 p_rating_of_record_pattern := l_special_info.segment4;
342 p_rating_of_record_period := fnd_date.canonical_to_date(l_special_info.segment6);
343 ELSE -- Added select for bug# 1389262
344 DECLARE
345 l_effective_date DATE;
346 BEGIN
347 SELECT MAX(pan.date_from)
348 INTO l_effective_date
349 FROM per_person_analyses pan,
350 fnd_id_flex_structures flx
351 WHERE pan.id_flex_num = flx.id_flex_num
352 AND flx.id_flex_code = 'PEA'
353 AND flx.application_id = 800
354 AND flx.id_flex_structure_code = 'US_FED_PERF_APPRAISAL'
355 AND pan.person_id = p_person_id;
356 ghr_api.return_special_information(p_person_id, 'US Fed Perf Appraisal',
357 l_effective_date, l_special_info);
358 IF l_special_info.object_version_number IS NOT NULL THEN
359 p_rating_of_record_level := l_special_info.segment5;
360 p_rating_of_record_pattern := l_special_info.segment4;
361 p_rating_of_record_period := fnd_date.canonical_to_date(l_special_info.segment6);
362 /* Pradeep commented this for the Bug 4005811
363 ELSE
364 raise NO_DATA_FOUND;
365 End of Bug 4005811*/
366 END IF;
367 /*EXCEPTION
368 WHEN NO_DATA_FOUND THEN
369 -- Generate entry in PROCESS_LOG
370 OPEN c_per;
371 FETCH c_per INTO l_emp_number;
372 CLOSE c_per;
373 ghr_mto_int.log_message(p_procedure => 'No US Fed Perf Appraisal Info',
374 p_message => 'Employee number ' || l_emp_number ||
375 ' does not have US Fed Perf Appraisal ' ||
376 'on ' || TO_CHAR(p_effective_date, 'DD-MON-YYYY'));*/
377 END;
378 END IF;
379 EXCEPTION
380 WHEN OTHERS THEN
381 p_rating_of_record_level := NULL;
382 p_rating_of_record_pattern := NULL;
383 p_rating_of_record_period := NULL;
384 raise;
385 END get_per_sit_perf_appraisal;
386 --
387 ---------------------------------------------------------------------------------------------
388 -- This one gets the details from the PAR Extra info for NOAC specific
389 ---------------------------------------------------------------------------------------------
390 PROCEDURE get_PAR_EI_noac (p_pa_request_id IN NUMBER
391 ,p_first_noa_id IN NUMBER
392 ,p_second_noa_id IN NUMBER
393 ,p_noa_family_code IN VARCHAR2
394 ,p_person_id IN NUMBER
395 ,p_effective_date IN DATE
396 ,p_creditable_military_service OUT NOCOPY VARCHAR2
397 ,p_frozen_service OUT NOCOPY VARCHAR2
398 ,p_from_retirement_coverage OUT NOCOPY VARCHAR2
399 ,p_race_national_origin OUT NOCOPY VARCHAR2
400 ,p_handicap_code OUT NOCOPY VARCHAR2
401 ,p_ind_group_award OUT NOCOPY VARCHAR2
402 ,p_benefit_award OUT NOCOPY VARCHAR2
403 ,p_race_ethnic_info OUT NOCOPY VARCHAR2) IS
404 --
405 -- Bug#5168568 Removed Information type GHR_US_PAR_ETHNICITY_RACE
406 -- Having this EIT in the list is skipping the printing of Military service, frozen service.
407 CURSOR c_rit IS
408 SELECT rit.information_type
409 FROM ghr_noa_families nfa
410 ,ghr_pa_request_info_types rit
411 WHERE rit.noa_family_code = nfa.noa_family_code
412 AND (nfa.nature_of_action_id = p_first_noa_id
413 OR nfa.nature_of_action_id = p_second_noa_id)
414 AND rit.information_type IN ('GHR_US_PAR_AWARDS_BONUS' ,'GHR_US_PAR_APPT_INFO'
415 ,'GHR_US_PAR_APPT_TRANSFER' ,'GHR_US_PAR_CONV_APP'
416 ,'GHR_US_PAR_RETURN_TO_DUTY','GHR_US_PAR_CHG_RETIRE_PLAN'
417 ,'GHR_US_PAR_CHG_SCD');
418 --
419 l_information_type ghr_pa_request_extra_info.information_type%TYPE;
420 l_rei ghr_pa_request_extra_info%ROWTYPE;
421 l_race_national_origin VARCHAR2(150);
422 l_handicap_code VARCHAR2(150);
423 l_per_ei_grp1_data per_people_extra_info%rowtype;
424
425 BEGIN
426 -- first get the information type for this NOA -- must only be one!!
427 FOR c_rit_rec IN c_rit LOOP
428 l_information_type := c_rit_rec.information_type;
429 END LOOP;
430
431 -- Only bother doing the rest if we got one we are interested in!
432 IF l_information_type IS NOT NULL THEN
433 get_PAR_EI (p_pa_request_id
434 ,p_noa_family_code
435 ,l_information_type
436 ,l_rei);
437 --
438
439 IF l_information_type = 'GHR_US_PAR_AWARDS_BONUS' THEN
440 p_ind_group_award := l_rei.rei_information6;
441 p_benefit_award := l_rei.rei_information7;
442 ELSIF l_information_type = 'GHR_US_PAR_APPT_INFO' THEN
443 p_creditable_military_service := SUBSTR(l_rei.rei_information4,1,4);
444 p_frozen_service := SUBSTR(l_rei.rei_information7,1,4);
445 p_from_retirement_coverage := l_rei.rei_information14;
446 l_race_national_origin := l_rei.rei_information16;
447 l_handicap_code := l_rei.rei_information8;
448 --
449 ELSIF l_information_type = 'GHR_US_PAR_APPT_TRANSFER' THEN
450 p_creditable_military_service := SUBSTR(l_rei.rei_information6,1,4);
451 p_frozen_service := SUBSTR(l_rei.rei_information9,1,4);
452 p_from_retirement_coverage := l_rei.rei_information16;
453 l_race_national_origin := l_rei.rei_information18;
454 l_handicap_code := l_rei.rei_information10;
455 --
456 ELSIF l_information_type = 'GHR_US_PAR_CONV_APP' THEN
457 p_creditable_military_service := SUBSTR(l_rei.rei_information4,1,4);
458 p_frozen_service := SUBSTR(l_rei.rei_information6,1,4);
459 p_from_retirement_coverage := l_rei.rei_information10;
460 l_race_national_origin := l_rei.rei_information12;
461 l_handicap_code := l_rei.rei_information7;
462 --
463 ELSIF l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' THEN
464 p_creditable_military_service := SUBSTR(l_rei.rei_information3,1,4);
465 p_frozen_service := SUBSTR(l_rei.rei_information5,1,4);
466 --
467 ELSIF l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' THEN
468 p_creditable_military_service := SUBSTR(l_rei.rei_information3,1,4);
469 p_frozen_service := SUBSTR(l_rei.rei_information5,1,4);
470 p_from_retirement_coverage := l_rei.rei_information6;
471 --
472 ELSIF l_information_type = 'GHR_US_PAR_CHG_SCD' THEN
473 p_creditable_military_service := SUBSTR(l_rei.rei_information5,1,4);
474 p_frozen_service := SUBSTR(l_rei.rei_information6,1,4);
475 p_from_retirement_coverage := l_rei.rei_information7;
476 -- -- Bug 4724337 Race or National Origin changes
477 ELSIF l_information_type = 'GHR_US_PAR_ETHNICITY_RACE' THEN
478 IF l_rei.rei_information3 IS NOT NULL OR
479 l_rei.rei_information4 IS NOT NULL OR
480 l_rei.rei_information5 IS NOT NULL OR
481 l_rei.rei_information6 IS NOT NULL OR
482 l_rei.rei_information7 IS NOT NULL OR
483 l_rei.rei_information8 IS NOT NULL THEN
484 p_race_ethnic_info := NVL(l_rei.rei_information3,'0') || NVL(l_rei.rei_information4,'0') || NVL(l_rei.rei_information5,'0') ||
485 NVL(l_rei.rei_information6,'0') || NVL(l_rei.rei_information7,'0') || NVL(l_rei.rei_information8,'0');
486 END IF; -- IF l_rei.rei_information3 IS NOT
487 -- End Bug 4724337 Race or National Origin changes
488 END IF; -- IF l_information_type = 'GHR_US_PAR_
489 END IF; -- IF l_information_type IS NOT NULL
490 --
491 -- bug 711711
492 -- if RNO or Handicap code was not filled then get them from HR Person EI
493 IF l_race_national_origin IS NULL
494 OR l_handicap_code IS NULL THEN
495 ghr_history_fetch.fetch_peopleei(
496 p_person_id => p_person_id,
497 p_information_type => 'GHR_US_PER_GROUP1',
498 p_date_effective => p_effective_date,
499 p_per_ei_data => l_per_ei_grp1_data);
500 --
501 IF l_race_national_origin IS NULL THEN
502 l_race_national_origin := l_per_ei_grp1_data.pei_information5;
503 END IF;
504 --
505 IF l_handicap_code IS NULL THEN
506 l_handicap_code := l_per_ei_grp1_data.pei_information11;
507 END IF;
508 END IF;
509
510 IF p_race_ethnic_info IS NULL THEN
511 -- Fetching Race and ethnicity category
512 l_per_ei_grp1_data := NULL; -- Bug 4724337
513 ghr_history_fetch.fetch_peopleei
514 (p_person_id => p_person_id,
515 p_information_type => 'GHR_US_PER_ETHNICITY_RACE',
516 p_date_effective => p_effective_date,
517 p_per_ei_data => l_per_ei_grp1_data
518 );
519 -- Populate Race only if atleast one data segment is entered.
520 IF l_per_ei_grp1_data.pei_information3 IS NOT NULL OR
521 l_per_ei_grp1_data.pei_information4 IS NOT NULL OR
522 l_per_ei_grp1_data.pei_information5 IS NOT NULL OR
523 l_per_ei_grp1_data.pei_information6 IS NOT NULL OR
524 l_per_ei_grp1_data.pei_information7 IS NOT NULL OR
525 l_per_ei_grp1_data.pei_information8 IS NOT NULL THEN
526 p_race_ethnic_info := NVL(l_per_ei_grp1_data.pei_information3,'0') || NVL(l_per_ei_grp1_data.pei_information4,'0') || NVL(l_per_ei_grp1_data.pei_information5,'0') ||
527 NVL(l_per_ei_grp1_data.pei_information6,'0') || NVL(l_per_ei_grp1_data.pei_information7,'0') || NVL(l_per_ei_grp1_data.pei_information8,'0');
528 END IF;
529 -- End Bug 4714292 EHRI Reports Changes for EOY 05
530 END IF;
531
532 p_race_national_origin := l_race_national_origin;
533 p_handicap_code := l_handicap_code;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 p_creditable_military_service := NULL;
538 p_frozen_service := NULL;
539 p_from_retirement_coverage := NULL;
540 p_race_national_origin := NULL;
541 p_handicap_code := NULL;
542 p_ind_group_award := NULL;
543 p_benefit_award := NULL;
544 p_race_ethnic_info := NULL;
545 raise;
546 END get_PAR_EI_noac;
547 --
548 ---------------------------------------------------------------------------------------------
549 -- This one gets the prior Work schedule and Pay Rate Determinant.
550 -- As an enhancement we should add these columns as well as prior duty station to the PAR
551 -- table since as is we have to go to history to get them!!
552 ---------------------------------------------------------------------------------------------
553 PROCEDURE get_prior_ws_prd_ds (p_pa_request_id IN NUMBER
554 ,p_altered_pa_request_id IN NUMBER
555 ,p_first_noa_id IN NUMBER
556 ,p_second_noa_id IN NUMBER
557 ,p_person_id IN NUMBER
558 ,p_employee_assignment_id IN NUMBER
559 ,p_from_position_id IN NUMBER
560 ,p_effective_date IN DATE
561 ,p_status IN VARCHAR2
562 ,p_from_work_schedule OUT NOCOPY VARCHAR2
563 ,p_from_pay_rate_determinant OUT NOCOPY VARCHAR2
564 ,p_from_duty_station_code OUT NOCOPY VARCHAR2) IS
565 --
566 l_pa_request_id NUMBER;
567 l_noa_id NUMBER;
568 --
569 l_asgei_data per_assignment_extra_info%ROWTYPE;
570 l_asgn_data per_all_assignments_f%ROWTYPE;
571 l_assignment_id NUMBER;
572 l_location_id NUMBER;
573 l_duty_station_id NUMBER;
574 l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
575 l_dummy_varchar VARCHAR2(2000);
576 l_dummy_number NUMBER;
577
578 --8275231
579 cursor get_first_noa_id
580 is
581 select first_noa_id
582 from ghr_pa_requests
583 where pa_request_id = (select min(pa_request_id)
584 from ghr_pa_requests
585 connect by pa_request_id = prior altered_pa_request_id
586 start with pa_request_id = p_pa_request_id);
587
588 cursor get_dual_det
589 is
590 select rpa_type,
591 mass_action_id,
592 first_noa_code,
593 second_noa_code
594 from ghr_pa_requests
595 where pa_request_id = p_pa_request_id;
596 --8275231
597
598 BEGIN
599 -- If the PAR has happened then need to go to history to get it
600 -- otherwise we can use the same procedure the form, update hr and edits uses
601 IF p_status = 'UPDATE_HR_COMPLETE' THEN
602 --
603 IF p_altered_pa_request_id IS NULL THEN -- ie nothing is really being corrected
604 l_pa_request_id := p_pa_request_id;
605 l_noa_id := p_first_noa_id;
606
607 --8275231
608 for rec_get_dual_det in get_dual_det
609 loop
610 if rec_get_dual_det.second_noa_code is not null and
611 rec_get_dual_det.first_noa_code not in ('001','002') then
612 open get_first_noa_id;
613 fetch get_first_noa_id into l_noa_id;
614 close get_first_noa_id;
615 end if;
616 end loop;
617 --8275231
618
619 ELSE
620 l_pa_request_id := p_altered_pa_request_id;
621 l_noa_id := p_second_noa_id;
622
623 --8275231
624 for rec_get_dual_det in get_dual_det
625 loop
626 if rec_get_dual_det.rpa_type = 'DUAL' and rec_get_dual_det.mass_action_id is not null then
627 open get_first_noa_id;
628 fetch get_first_noa_id into l_noa_id;
629 close get_first_noa_id;
630 end if;
631 end loop;
632 --8275231
633 END IF;
634 --
635 -- Only need to even attempt to get these details is from position id is given!!
636 -- This means we wills tive give values for 5__ NOAC's even though the guide says
637 -- they are not needed?
638 --
639 IF p_from_position_id IS NOT NULL THEN
640 GHR_HISTORY_FETCH.fetch_asgei_prior_root_sf50(p_assignment_id => p_employee_assignment_id
641 ,p_information_type => 'GHR_US_ASG_SF52'
642 ,p_altered_pa_request_id => l_pa_request_id
643 ,p_noa_id_corrected => l_noa_id
644 ,p_date_effective => p_effective_date
645 ,p_asgei_data => l_asgei_data);
646 --
647 p_from_work_schedule := l_asgei_data.aei_information7;
648 p_from_pay_rate_determinant := l_asgei_data.aei_information6;
649 --
650 -- Now lets go get the location id which gives us the duty station
651 GHR_HISTORY_FETCH.fetch_asgn_prior_root_sf50(p_assignment_id => p_employee_assignment_id
652 ,p_altered_pa_request_id => l_pa_request_id
653 ,p_noa_id_corrected => l_noa_id
654 ,p_date_effective => p_effective_date
655 ,p_assignment_data => l_asgn_data);
656 --
657 ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_asgn_data.location_id
658 ,l_duty_station_id);
659 --
660 ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
661 ,p_effective_date
662 ,l_duty_station_code
663 ,l_dummy_varchar);
664 p_from_duty_station_code := l_duty_station_code;
665 END IF;
666 --
667 ELSE -- FUTURE_ACTION's
668 IF p_from_position_id IS NOT NULL THEN
669 l_assignment_id := p_employee_assignment_id;
670 GHR_API.sf52_from_data_elements
671 (p_person_id => p_person_id
672 ,p_assignment_id => l_assignment_id
673 ,p_effective_date => p_effective_date
674 ,p_altered_pa_request_id => null
675 ,p_noa_id_corrected => null
676 ,p_pa_history_id => null
677 ,p_position_id => l_dummy_number
678 ,p_position_title => l_dummy_varchar
679 ,p_position_number => l_dummy_varchar
680 ,p_position_seq_no => l_dummy_number
681 ,p_pay_plan => l_dummy_varchar
682 ,p_job_id => l_dummy_number
683 ,p_occ_code => l_dummy_varchar
684 ,p_grade_id => l_dummy_number
685 ,p_grade_or_level => l_dummy_varchar
686 ,p_step_or_rate => l_dummy_varchar
687 ,p_total_salary => l_dummy_number
688 ,p_pay_basis => l_dummy_varchar
689 -- FWFA Changes Bug#4444609
690 ,p_pay_table_identifier => l_dummy_number
691 -- FWFA Changes
692 ,p_basic_pay => l_dummy_number
693 ,p_locality_adj => l_dummy_number
694 ,p_adj_basic_pay => l_dummy_number
695 ,p_other_pay => l_dummy_number
696 ,p_au_overtime => l_dummy_number
697 ,p_auo_premium_pay_indicator => l_dummy_varchar
698 ,p_availability_pay => l_dummy_number
699 ,p_ap_premium_pay_indicator => l_dummy_varchar
700 ,p_retention_allowance => l_dummy_number
701 ,p_retention_allow_percentage=> l_dummy_number
702 ,p_supervisory_differential => l_dummy_number
703 ,p_supervisory_diff_percentage=> l_dummy_number
704 ,p_staffing_differential => l_dummy_number
705 ,p_staffing_diff_percentage => l_dummy_number
706 ,p_organization_id => l_dummy_number
707 ,p_position_org_line1 => l_dummy_varchar
708 ,p_position_org_line2 => l_dummy_varchar
709 ,p_position_org_line3 => l_dummy_varchar
710 ,p_position_org_line4 => l_dummy_varchar
711 ,p_position_org_line5 => l_dummy_varchar
712 ,p_position_org_line6 => l_dummy_varchar
713 ,p_duty_station_location_id => l_location_id
714 ,p_pay_rate_determinant => p_from_pay_rate_determinant
715 ,p_work_schedule => p_from_work_schedule);
716 --
717 ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_location_id
718 ,l_duty_station_id);
719
720 ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
721 ,p_effective_date
722 ,l_duty_station_code
723 ,l_dummy_varchar);
724 p_from_duty_station_code := l_duty_station_code;
725 --
726 END IF;
727 END IF;
728 EXCEPTION
729 WHEN OTHERS THEN
730 p_from_work_schedule := NULL;
731 p_from_pay_rate_determinant := NULL;
732 p_from_duty_station_code := NULL;
733 raise;
734 END get_prior_ws_prd_ds;
735 --
736 ---------------------------------------------------------------------------------------------
737 -- For a 'Correction' record it gets the previous SSN if that is what is being corrected.
738 ---------------------------------------------------------------------------------------------
739 PROCEDURE get_prev_ssn (p_altered_pa_request_id IN NUMBER
740 ,p_employee_national_identifier IN VARCHAR2
741 ,p_noa_family_code IN VARCHAR2
742 ,p_from_national_identifier OUT NOCOPY VARCHAR2) IS
743 --
744 CURSOR cur_prev_ssn IS
745 SELECT par.employee_national_identifier prev_ssn
746 FROM ghr_pa_requests par
747 WHERE par.pa_request_id = p_altered_pa_request_id;
748 --
749 BEGIN
750 IF p_noa_family_code = 'CORRECT' THEN
751 FOR cur_prev_ssn_rec IN cur_prev_ssn LOOP
752 IF p_employee_national_identifier <> cur_prev_ssn_rec.prev_ssn THEN
753 p_from_national_identifier := format_ni(cur_prev_ssn_rec.prev_ssn);
754 END IF;
755 END LOOP;
756 END IF;
757 EXCEPTION
758 WHEN OTHERS THEN
759 p_from_national_identifier := NULL;
760 raise;
761 END;
762 --
763
764 FUNCTION get_equivalent_pay_plan(p_pay_plan IN ghr_pay_plans.pay_plan%TYPE)
765 RETURN VARCHAR2 IS
766 l_result ghr_pay_plans.equivalent_pay_plan%TYPE;
767 BEGIN
768 --9862674 added NVL
769 SELECT NVL(equivalent_pay_plan, '@#')
770 INTO l_result
771 FROM ghr_pay_plans
772 WHERE pay_plan = p_pay_plan;
773 RETURN l_result;
774 EXCEPTION
775 WHEN NO_DATA_FOUND THEN
776 l_result := NULL;
777 RETURN l_result;
778 END get_equivalent_pay_plan;
779 -- Function get_loc_pay_area_code returns the LOCALITY PAY AREA CODE attached to the Duty station.
780 -- Bug# 3231946 Added parameter p_duty_station_code to fix the bug.
781 -- With the addition of new parameter, this function can be used to find
782 -- the locality pay area code by passing either duty_station_id or duty_station_code.
783 FUNCTION get_loc_pay_area_code(
784 p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE default NULL,
785 p_duty_station_code IN ghr_duty_stations_f.duty_station_code%TYPE default NULL,
786 p_effective_date IN DATE)
787 RETURN VARCHAR2 IS
788 l_result ghr_locality_pay_areas_f.locality_pay_area_code%TYPE;
789 BEGIN
790
791 IF p_duty_station_id is NOT NULL THEN
792 SELECT lpa.locality_pay_area_code
793 INTO l_result
794 FROM ghr_locality_pay_areas_f lpa
795 ,ghr_duty_stations_f dst
796 WHERE dst.duty_station_id = p_duty_station_id
797 AND NVL(p_effective_date,TRUNC(sysdate))
798 BETWEEN dst.effective_start_date and dst.effective_end_date
799 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
800 AND NVL(p_effective_date,TRUNC(sysdate))
801 BETWEEN lpa.effective_start_date and lpa.effective_end_date;
802 ELSIF p_duty_station_code is NOT NULL THEN
803 SELECT lpa.locality_pay_area_code
804 INTO l_result
805 FROM ghr_locality_pay_areas_f lpa
806 ,ghr_duty_stations_f dst
807 WHERE dst.duty_station_code = p_duty_station_code
808 AND NVL(p_effective_date,TRUNC(sysdate))
809 BETWEEN dst.effective_start_date and dst.effective_end_date
810 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
811 AND NVL(p_effective_date,TRUNC(sysdate))
812 BETWEEN lpa.effective_start_date and lpa.effective_end_date;
813 END IF;
814
815 RETURN l_result;
816 EXCEPTION
817 WHEN NO_DATA_FOUND THEN
818 l_result := NULL;
819 RETURN l_result;
820 WHEN OTHERS THEN
821 l_result := NULL;
822 RETURN l_result;
823 END get_loc_pay_area_code;
824
825 ---------------------------------------------------------------------------------------------
826 -- C) This section includes any formating needed for certain fields.
827 -- Note: some formating is done in the report, but if possible it should be done here!
828 ---------------------------------------------------------------------------------------------
829 --
830 ---------------------------------------------------------------------------------------------
831 -- This function takes the standard ni format ie 999-99-9999
832 -- and returns it without the -'s
833 ---------------------------------------------------------------------------------------------
834 FUNCTION format_ni(p_ni IN VARCHAR2)
835 RETURN VARCHAR2 IS
836 BEGIN
837 RETURN(REPLACE(p_ni,'-') );
838 END format_ni;
839 --
840 ---------------------------------------------------------------------------------------------
841 -- This function takes the standard possibly 4 char noa code and
842 -- if it is 4 long returns the last three chars!
843 ---------------------------------------------------------------------------------------------
844 FUNCTION format_noac(p_noac IN VARCHAR2)
845 RETURN VARCHAR2 IS
846 BEGIN
847 IF LENGTH (p_noac) = 4 THEN
848 RETURN(SUBSTR(p_noac,2,3) );
849 END IF;
850 RETURN(p_noac);
851 END format_noac;
852 --
853 ---------------------------------------------------------------------------------------------
854 -- This function takes the duty station code and if the first 2 positions
855 -- are chars replaces the last 3 chars with zeros!
856 -- i.e. the duty station is 1) foreign OR 2)US Possesion or 3) US administered Area
857 ---------------------------------------------------------------------------------------------
858 FUNCTION format_ds(p_duty_station_code IN VARCHAR2)
859 RETURN VARCHAR2 IS
860 l_ds_2chars VARCHAR2(2);
861 BEGIN
862 l_ds_2chars := TRANSLATE(SUBSTR(upper(p_duty_station_code),1,2),
863 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','**************************');
864 IF l_ds_2chars = '**' THEN
865 RETURN(SUBSTR(p_duty_station_code,1,6)||'000' );
866 ELSE
867 RETURN(p_duty_station_code);
868 END IF;
869 END format_ds;
870 --
871 ---------------------------------------------------------------------------------------------
872 -- This function takes the employees first last and middle names and puts them into the
873 -- format: last name comma first name space middle names - no longer than 23 chars
874 ---------------------------------------------------------------------------------------------
875 FUNCTION format_name (p_first_name IN VARCHAR2
876 ,p_last_name IN VARCHAR2
877 ,p_middle_name IN VARCHAR2)
878 RETURN VARCHAR2 IS
879 BEGIN
880 RETURN(SUBSTR(p_last_name||','||p_first_name||' '||p_middle_name,1,23) );
881 END format_name;
882 ---------------------------------------------------------------------------------------------
883 -- This function takes the any of the employees first, last or middle names and puts
884 -- them back such that its not longer than 35 chars
885 -- Added format_name_ehri for EHRI changes. The names cannot exceed more than 35 chars
886 ---------------------------------------------------------------------------------------------
887 FUNCTION format_name_ehri (p_name IN VARCHAR2)
888 RETURN VARCHAR2 IS
889 BEGIN
890 RETURN(SUBSTR(p_name,1,35) );
891 END format_name_ehri;
892 ---------------------------------------------------------------------------------------------
893 -- This function will format the basic pay depending on the pay basis
894 ---------------------------------------------------------------------------------------------
895 FUNCTION format_basic_pay(p_basic_pay IN NUMBER
896 ,p_pay_basis IN VARCHAR2
897 ,p_size IN NUMBER)
898 RETURN VARCHAR2 IS
899 BEGIN
900 IF p_pay_basis IN ('PA','SY','PM','BW') THEN
901 -- just report dollars
902 IF p_basic_pay IS NOT NULL THEN
903 RETURN ( LPAD(ROUND(p_basic_pay),p_size,'0') );
904 ELSE
905 RETURN(NULL);
906 END IF;
907 ELSIF p_pay_basis = 'WC' THEN
908 -- report all zero's
909 RETURN(SUBSTR('000000000000000000000',1,p_size) );
910 ELSE
911 -- report dollar and cents without decimal
912 IF p_basic_pay IS NOT NULL THEN
913 RETURN ( LPAD(ROUND(p_basic_pay,2) * 100,p_size,'0') );
914 ELSE
915 RETURN(NULL);
916 END IF;
917 END IF;
918 --
919 END format_basic_pay;
920 --
921 ---------------------------------------------------------------------------------------------
922 -- This function is very simple just left pads the amount that was passed in with zero's to
923 -- the size given
924 ---------------------------------------------------------------------------------------------
925 FUNCTION format_amount(p_amount IN NUMBER
926 ,p_size IN NUMBER)
927 RETURN VARCHAR2 IS
928 BEGIN
929 IF p_amount IS NOT NULL THEN
930 RETURN ( LPAD(p_amount,p_size,'0') );
931 ELSE
932 RETURN(NULL);
933 END IF;
934 --
935 END format_amount;
936 --
937
938 ---------------------------------------------------------------------------------------------
939 -- Bug# 5417021 This function left pads the 100 multiplier of the amount(rounded to two places of decimal)
940 -- that was passed in with zero's to the size given.
941 -- This is used just for award_percentage field.
942 ---------------------------------------------------------------------------------------------
943 FUNCTION format_award_perc(p_amount IN NUMBER
944 ,p_size IN NUMBER)
945 RETURN VARCHAR2 IS
946 BEGIN
947 IF p_amount IS NOT NULL THEN
948 RETURN ( LPAD((round(p_amount,2)*100),p_size,'0') );
949 ELSE
950 RETURN(NULL);
951 END IF;
952 --
953 END format_award_perc;
954 --
955
956 ---------------------------------------------------------------------------------------------
957 -- This will insert one record into the GHR_CPDF_TEMP
958 ---------------------------------------------------------------------------------------------
959 PROCEDURE insert_row (p_ghr_cpdf_temp_rec IN ghr_cpdf_temp%rowtype) IS
960 BEGIN
961 INSERT INTO ghr_cpdf_temp(
962 report_type,
963 session_id,
964 agency_code,
965 organizational_component,
966 personnel_office_id,
967 to_national_identifier,
968 employee_date_of_birth,
969 veterans_preference,
970 tenure,
971 service_comp_date,
972 retirement_plan,
973 creditable_military_service,
974 frozen_service,
975 from_retirement_coverage,
976 veterans_status,
977 sex,
978 race_national_origin,
979 handicap_code,
980 first_noa_code,
981 second_noa_code,
982 first_action_la_code1,
983 first_action_la_code2,
984 effective_date,
985 to_pay_plan,
986 to_occ_code,
987 to_grade_or_level,
988 to_step_or_rate,
989 to_basic_pay,
990 to_pay_basis,
991 to_work_schedule,
992 to_pay_rate_determinant,
993 position_occupied,
994 supervisory_status,
995 to_duty_station_code,
996 current_appointment_auth1,
997 current_appointment_auth2,
998 rating_of_record_level,
999 rating_of_record_pattern,
1000 rating_of_record_period_ends,
1001 individual_group_award,
1002 award_amount,
1003 benefit_amount,
1004 employee_last_name,
1005 from_pay_plan,
1006 from_occ_code,
1007 from_grade_or_level,
1008 from_step_or_rate,
1009 from_basic_pay,
1010 from_pay_basis,
1011 from_work_schedule,
1012 from_pay_rate_determinant,
1013 from_national_identifier,
1014 from_locality_adj,
1015 from_duty_station_code,
1016 to_locality_adj,
1017 to_staffing_differential,
1018 to_supervisory_differential,
1019 to_retention_allowance,
1020 education_level,
1021 academic_discipline,
1022 year_degree_attained,
1023 -- employee_last_name,
1024 employee_first_name,
1025 employee_middle_names,
1026 name_title,
1027 position_title,
1028 award_dollars,
1029 award_hours,
1030 award_percentage,
1031 SCD_retirement,
1032 SCD_rif ,
1033 race_ethnic_info
1034 )
1035 VALUES(
1036 'DYNAMICS',
1037 USERENV('SESSIONID'),
1038 p_ghr_cpdf_temp_rec.agency_code,
1039 p_ghr_cpdf_temp_rec.organizational_component,
1040 p_ghr_cpdf_temp_rec.personnel_office_id,
1041 p_ghr_cpdf_temp_rec.to_national_identifier,
1042 p_ghr_cpdf_temp_rec.employee_date_of_birth,
1043 p_ghr_cpdf_temp_rec.veterans_preference,
1044 p_ghr_cpdf_temp_rec.tenure,
1045 p_ghr_cpdf_temp_rec.service_comp_date,
1046 p_ghr_cpdf_temp_rec.retirement_plan,
1047 p_ghr_cpdf_temp_rec.creditable_military_service,
1048 p_ghr_cpdf_temp_rec.frozen_service,
1049 p_ghr_cpdf_temp_rec.from_retirement_coverage,
1050 p_ghr_cpdf_temp_rec.veterans_status,
1051 p_ghr_cpdf_temp_rec.sex,
1052 p_ghr_cpdf_temp_rec.race_national_origin,
1053 p_ghr_cpdf_temp_rec.handicap_code,
1054 p_ghr_cpdf_temp_rec.first_noa_code,
1055 p_ghr_cpdf_temp_rec.second_noa_code,
1056 p_ghr_cpdf_temp_rec.first_action_la_code1,
1057 p_ghr_cpdf_temp_rec.first_action_la_code2,
1058 p_ghr_cpdf_temp_rec.effective_date,
1059 p_ghr_cpdf_temp_rec.to_pay_plan,
1060 p_ghr_cpdf_temp_rec.to_occ_code,
1061 p_ghr_cpdf_temp_rec.to_grade_or_level,
1062 p_ghr_cpdf_temp_rec.to_step_or_rate,
1063 p_ghr_cpdf_temp_rec.to_basic_pay,
1064 p_ghr_cpdf_temp_rec.to_pay_basis,
1065 p_ghr_cpdf_temp_rec.to_work_schedule,
1066 p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1067 p_ghr_cpdf_temp_rec.position_occupied,
1068 p_ghr_cpdf_temp_rec.supervisory_status,
1069 p_ghr_cpdf_temp_rec.to_duty_station_code,
1070 p_ghr_cpdf_temp_rec.current_appointment_auth1,
1071 p_ghr_cpdf_temp_rec.current_appointment_auth2,
1072 p_ghr_cpdf_temp_rec.rating_of_record_level,
1073 p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1074 p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1075 p_ghr_cpdf_temp_rec.individual_group_award,
1076 p_ghr_cpdf_temp_rec.award_amount,
1077 p_ghr_cpdf_temp_rec.benefit_amount,
1078 p_ghr_cpdf_temp_rec.employee_last_name,
1079 p_ghr_cpdf_temp_rec.from_pay_plan,
1080 p_ghr_cpdf_temp_rec.from_occ_code,
1081 p_ghr_cpdf_temp_rec.from_grade_or_level,
1082 p_ghr_cpdf_temp_rec.from_step_or_rate,
1083 p_ghr_cpdf_temp_rec.from_basic_pay,
1084 p_ghr_cpdf_temp_rec.from_pay_basis,
1085 p_ghr_cpdf_temp_rec.from_work_schedule,
1086 p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1087 p_ghr_cpdf_temp_rec.from_national_identifier,
1088 p_ghr_cpdf_temp_rec.from_locality_adj,
1089 p_ghr_cpdf_temp_rec.from_duty_station_code,
1090 p_ghr_cpdf_temp_rec.to_locality_adj,
1091 p_ghr_cpdf_temp_rec.to_staffing_differential,
1092 p_ghr_cpdf_temp_rec.to_supervisory_differential,
1093 p_ghr_cpdf_temp_rec.to_retention_allowance,
1094 p_ghr_cpdf_temp_rec.education_level,
1095 p_ghr_cpdf_temp_rec.academic_discipline,
1096 p_ghr_cpdf_temp_rec.year_degree_attained,
1097 -- p_ghr_cpdf_temp_rec.employee_last_name,
1098 p_ghr_cpdf_temp_rec.employee_first_name,
1099 p_ghr_cpdf_temp_rec.employee_middle_names,
1100 p_ghr_cpdf_temp_rec.name_title,
1101 p_ghr_cpdf_temp_rec.position_title,
1102 p_ghr_cpdf_temp_rec.award_dollars,
1103 p_ghr_cpdf_temp_rec.award_hours,
1104 p_ghr_cpdf_temp_rec.award_percentage,
1105 p_ghr_cpdf_temp_rec.SCD_retirement,
1106 p_ghr_cpdf_temp_rec.SCD_rif,
1107 p_ghr_cpdf_temp_rec.race_ethnic_info
1108 );
1109
1110 COMMIT;
1111
1112 END insert_row;
1113
1114
1115 PROCEDURE get_suffix_lname(p_last_name in varchar2,
1116 p_report_date in date,
1117 p_suffix out nocopy varchar2,
1118 p_lname out nocopy varchar2)
1119 IS
1120 l_suffix_pos number;
1121 l_total_len number;
1122 l_proc varchar2(30) := 'get_suffix_lname';
1123
1124 CURSOR GET_SUFFIX IS
1125 SELECT INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
1126 LENGTH(p_last_name)
1127 FROM HR_LOOKUPS
1128 WHERE LOOKUP_TYPE = 'GHR_US_NAME_SUFFIX'
1129 AND TRUNC(p_report_date) BETWEEN NVL(START_DATE_ACTIVE,p_report_date)
1130 AND NVL(END_DATE_ACTIVE,p_report_date)
1131 AND RTRIM(SUBSTR(TRANSLATE(UPPER(p_last_name),',.',' '),
1132 INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
1133 LENGTH(p_last_name)),' ') = ' '||UPPER(LOOKUP_CODE)
1134 AND ROWNUM = 1;
1135 BEGIN
1136
1137 hr_utility.set_location('Entering:'||l_proc,5);
1138
1139 IF GET_SUFFIX%ISOPEN THEN
1140 CLOSE GET_SUFFIX;
1141 END IF;
1142
1143 OPEN GET_SUFFIX;
1144 --getting the position of a suffix appended in the lastname by comparing the lastname
1145 -- with the suffixes available in lookup*/
1146 FETCH GET_SUFFIX INTO l_suffix_pos, l_total_len;
1147 -- if the suffix is not found then returning the lastname by removing special characters
1148 IF GET_SUFFIX%NOTFOUND THEN
1149 p_lname := RTRIM(p_last_name,' ,.');
1150 p_suffix := NULL;
1151 -- if the suffix is found then returning the lastname by removing special characters
1152 -- with suffix
1153 ELSE
1154 p_lname := RTRIM(SUBSTR(p_last_name, 0, l_suffix_pos-1),' ,.');
1155 p_suffix := SUBSTR(p_last_name,l_suffix_pos+1,l_total_len);
1156 END IF;
1157 CLOSE GET_SUFFIX;
1158 END get_suffix_lname;
1159
1160 --
1161 ---------------------------------------------------------------------------------------------
1162 -- This is the main procedure
1163 ---------------------------------------------------------------------------------------------
1164 PROCEDURE populate_ghr_cpdf_temp(p_agency IN VARCHAR2
1165 ,p_start_date IN DATE
1166 ,p_end_date IN DATE
1167 ,p_count_only IN BOOLEAN ) IS
1168 --
1169 l_proc VARCHAR2(72) := g_package||'populate_ghr_cpdf_temp';
1170 --
1171 l_info_type VARCHAR2(200) := NULL;
1172 l_api_assignment_id per_assignments.assignment_id%TYPE;
1173 --
1174 l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
1175 l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
1176 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
1177 l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
1178 l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
1179 l_multi_error_flag boolean;
1180 --
1181 -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
1182 -- selected by a user in the given period
1183 -- may as well select everyting from the PA Request table (saves going back!)
1184
1185 -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
1186 -- records based on business group id/security group id
1187 -- Not added outer join for per_people_f because we are expecting existence of person_id
1188 -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
1189
1190 -- 24-OCT-2002 JH truncated sf50_approval_date because it does contain time on the db.
1191 -- which causes some rows to not be included on last day.
1192
1193 CURSOR cur_get_pars IS
1194 SELECT par.*
1195 FROM ghr_pa_requests par,
1196 per_people_f per
1197 WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
1198 AND par.person_id = per.person_id
1199 AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
1200 AND per.effective_end_date
1201 AND trunc(par.sf50_approval_date) BETWEEN p_start_date AND p_end_date
1202 --bug #6976546 removed 'FUTURE_ACTION'
1203 AND par.status IN ('UPDATE_HR_COMPLETE')
1204 AND par.effective_date >= add_months(p_end_date,-24)
1205 --Bug # 10158843 added Bug # 9451305 removed effective date comparison with 6 months future to end date
1206 AND par.effective_date <= p_end_date --add_months(p_end_date,6)
1207 AND exclude_agency(NVL(par.agency_code,par.from_agency_code)) <> 'TRUE'
1208 AND exclude_noac(par.first_noa_code,par.second_noa_code,par.noa_family_code) <> 'TRUE'
1209 --Bug # 10158391 added the union condition to consider the records approved in last month and effective in the reporting period
1210 UNION
1211 SELECT par.*
1212 FROM ghr_pa_requests par,
1213 per_people_f per
1214 WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
1215 AND par.person_id = per.person_id
1216 AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
1217 AND per.effective_end_date
1218 --Bug # 11938755 removed approval date validation
1219 --AND trunc(par.sf50_approval_date) BETWEEN add_months(p_start_date,-1) AND p_start_date
1220 --bug #6976546 removed 'FUTURE_ACTION'
1221 AND par.status IN ('UPDATE_HR_COMPLETE')
1222 AND par.effective_date between p_start_date and p_end_date
1223 AND exclude_agency(NVL(par.agency_code,par.from_agency_code)) <> 'TRUE'
1224 AND exclude_noac(par.first_noa_code,par.second_noa_code,par.noa_family_code) <> 'TRUE';
1225
1226 --
1227
1228 --
1229 -- Note:
1230 -- 1) The report calls this procedure in the before report trigger passing in the
1231 -- the two parameters the user actually passes in as agency code and subelement
1232 -- as one here called p_agency by concatanating the two and adding a %!!
1233 -- 2) The AGENCY_CODE field should actually be thought of as the TO_AGENCY_CODE
1234 -- field as it is only populated if there is a TO position otherwise the FROM_AGENCY_CODE
1235 -- will be populated. When update HR is succesful we must have one of these.
1236 -- Bug 706585 has been raised to make sure it gets populated on UPDATE_HR and not UPDATE_HR_COMPLETE
1237 -- otherwise this will not be available to FUTURE_ACTIONS
1238 -- 3) ordering of the records will be done in the report, by agency code then ssn
1239
1240 l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
1241 l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
1242 l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
1243 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
1244 l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
1245 l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
1246 l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1247 l_sf52_rec1 ghr_pa_requests%ROWTYPE;
1248 l_sf52_rec2 ghr_pa_requests%ROWTYPE;
1249 l_dual_flg BOOLEAN:=FALSE;
1250 l_single_flg BOOLEAN:=TRUE;
1251 l_loop NUMBER :=1;
1252 l_index NUMBER :=1;
1253
1254 --Bug#2789704
1255 l_log_text ghr_process_log.log_text%type;
1256 l_message_name ghr_process_log.message_name%type;
1257 l_log_date ghr_process_log.log_date%type;
1258
1259 -- Bug 4542476
1260 l_locality_pay_area_code ghr_locality_pay_areas_f.locality_pay_area_code%type;
1261 l_equiv_plan ghr_pay_plans.equivalent_pay_plan%type;
1262 -- End Bug 4542476
1263
1264
1265 CURSOR cur_per_details(p_person_id per_all_people_f.person_id%type)
1266 IS
1267 SELECT title,last_name
1268 FROM per_all_people_f
1269 WHERE person_id = p_person_id;
1270
1271 CURSOR cur_scd_dates(p_pa_request_id ghr_pa_requests.pa_request_id%type)
1272 IS
1273 SELECT REI_INFORMATION3 rif ,REI_INFORMATION8 ret
1274 FROM ghr_pa_request_extra_info parei
1275 WHERE parei.pa_request_id=p_pa_request_id
1276 AND parei.information_type='GHR_US_PAR_CHG_SCD';
1277
1278 --8275231
1279 Cursor c_noa_family(p_noa_id in number,
1280 p_effective_date in date)
1281 is
1282 Select fam.noa_family_code
1283 from ghr_noa_families nof,
1284 ghr_families fam
1285 where nof.nature_of_action_id = p_noa_id
1286 and fam.noa_family_code = nof.noa_family_code
1287 and nvl(fam.proc_method_flag,hr_api.g_varchar2) = 'Y'
1288 and p_effective_date
1289 between nvl(fam.start_date_active,p_effective_date)
1290 and nvl(fam.end_date_active,p_effective_date);
1291
1292
1293 --8275231
1294
1295 l_records_found BOOLEAN;
1296 l_mesgbuff1 VARCHAR2(4000);
1297 l_scd_rif ghr_pa_request_extra_info.rei_information3%type;
1298 l_scd_ret ghr_pa_request_extra_info.rei_information8%type;
1299 ll_per_ei_data per_people_extra_info%rowtype;
1300 l_last_name per_all_people_f.last_name%type;
1301 l_suffix per_all_people_f.title%type;
1302
1303 -- For Dual Actions PRD is becoming null so preserving it using a local variable.
1304 l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
1305
1306 --Bug # 9329643
1307 l_employee_number per_people_f.employee_number%TYPE;
1308
1309 BEGIN
1310 --
1311 l_records_found:=FALSE;
1312 -- hr_utility.trace_on(null,'venkat');
1313 hr_utility.set_location('Entering:'||l_proc, 10);
1314 --
1315 ghr_mto_int.set_log_program_name('GHR_CPDF_DYNRPT');
1316 --
1317 IF p_end_date > p_start_Date THEN
1318
1319 FOR cur_get_pars_rec IN cur_get_pars
1320 LOOP
1321 hr_utility.set_location(l_proc||' Get PA Request data', 20);
1322 -- 1) Get PA Request data
1323 l_ghr_pa_requests_rec := cur_get_pars_rec;
1324
1325 l_sf52_rec1 := l_ghr_pa_requests_rec;
1326 l_sf52_rec2 := l_ghr_pa_requests_rec;
1327
1328 -- Begin Bug# 5109841
1329 IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
1330 AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
1331 l_loop := 2;
1332 l_dual_flg := TRUE;
1333 l_single_flg := FALSE;
1334 ELSE
1335 l_loop := 1;
1336 l_single_flg := TRUE;
1337 l_dual_flg := FALSE;
1338 END IF;
1339
1340 /*
1341 If ( l_ghr_pa_requests_rec.first_noa_code like '3%'and
1342 l_ghr_pa_requests_rec.second_noa_code ='825' ) THEN
1343 l_loop := 2;
1344 l_dual_flg := TRUE;
1345 l_single_flg := FALSE;
1346 ELSE
1347 l_loop := 1;
1348 l_single_flg := TRUE;
1349 l_dual_flg := FALSE;
1350 END IF;
1351 */
1352 -- End Bug# 5109841
1353 --
1354 /* COMMENTED THE ENTIRE IF CONDITION TO FIX BUG 3212482
1355 -- For Correction record need to build the whole lot up!
1356 -- OK it appears this function creates the whole 52 again! i.e switches the
1357 -- second noa details into the first no details
1358 Bug # 2884948 With New Correction NPA changes, we are saving the all the to side pay data in
1359 ghr_pa_requests table. So no need to call build_corrected_sf52
1360 IF l_ghr_pa_requests_rec.noa_family_code = 'CORRECT' THEN
1361 l_first_noa_id := l_ghr_pa_requests_rec.first_noa_id;
1362 l_first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
1363 l_first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
1364 l_first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
1365 --
1366
1367 ghr_corr_canc_sf52.build_corrected_sf52
1368 (p_pa_request_id => l_ghr_pa_requests_rec.pa_request_id
1369 ,p_noa_code_correct => l_ghr_pa_requests_rec.second_noa_code
1370 ,p_sf52_data_result => l_ghr_pa_requests_rec);
1371
1372 --
1373 l_ghr_pa_requests_rec.second_noa_id := l_ghr_pa_requests_rec.first_noa_id;
1374 l_ghr_pa_requests_rec.second_noa_code := l_ghr_pa_requests_rec.first_noa_code;
1375 l_ghr_pa_requests_rec.second_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
1376 l_ghr_pa_requests_rec.second_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
1377
1378 l_ghr_pa_requests_rec.first_noa_id := l_first_noa_id;
1379 l_ghr_pa_requests_rec.first_noa_code := l_first_noa_code;
1380 l_ghr_pa_requests_rec.first_action_la_code1 := l_first_action_la_code1;
1381 l_ghr_pa_requests_rec.first_action_la_code2 := l_first_action_la_code2;
1382 l_ghr_pa_requests_rec.noa_family_code := 'CORRECT';
1383
1384 END IF;
1385 */
1386 --
1387 -- 2) Do any further checks to see if this PAR record should be included in the report:
1388 --
1389 FOR l_index in 1..l_loop
1390 LOOP
1391 BEGIN
1392 -- Loop twice for dual action
1393 IF ( l_dual_flg = TRUE and l_index = 1 ) then
1394 --6850492 modified for dual action to assign pay rate determinant
1395 /* l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
1396 ghr_process_sf52.assign_new_rg( p_action_num => 1,
1397 p_pa_req => l_sf52_rec1);
1398
1399 l_ghr_pa_requests_rec := l_sf52_rec1;
1400 if l_sf52_rec1.pay_rate_determinant is null then
1401 l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
1402 end if; */
1403 --8275231
1404 ghr_process_sf52.null_2ndNoa_cols(l_sf52_rec1);
1405 l_ghr_pa_requests_rec := l_sf52_rec1;
1406 ELSIF ( l_dual_flg = TRUE and l_index = 2 ) then
1407 -- In case of Dual Actin assign_new_rg is nulling out the PRD.
1408 /*l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
1409 ghr_process_sf52.assign_new_rg( p_action_num => 2,
1410 p_pa_req => l_sf52_rec2);
1411
1412 l_ghr_pa_requests_rec := l_sf52_rec2;
1413 if l_sf52_rec2.pay_rate_determinant is null then
1414 l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
1415 end if; */
1416 --8275231
1417 ghr_process_sf52.copy_2ndNoa_to_1stNoa(l_sf52_rec2);
1418 ghr_process_sf52.null_2ndNoa_cols(l_sf52_rec2);
1419 for noa_family_rec in c_noa_family(l_sf52_rec2.first_noa_id,l_sf52_rec2.effective_date) loop --Bug# 8275231
1420 l_sf52_rec2.noa_family_code := noa_family_rec.noa_family_code;
1421 end loop;
1422 --8275231
1423 l_ghr_pa_requests_rec := l_sf52_rec2;
1424 l_dual_flg := FALSE;
1425 ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
1426 l_ghr_pa_requests_rec := cur_get_pars_rec;
1427 END IF;
1428
1429 --- 8490723/8490327 formating the noac before doing any comparison
1430 --- 9184710 Modified the parameter of passing as second NOAC should be NULl for DUal Actions
1431 l_ghr_pa_requests_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
1432 l_ghr_pa_requests_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);
1433
1434 -- Bug# 4648811getting the suffix from the Employee lastname and also removing suffix from lastname
1435 get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
1436 l_ghr_pa_requests_rec.effective_date,
1437 l_suffix,
1438 l_last_name);
1439 --End Bug# 4648811
1440
1441 --Bug # 9329643
1442 If l_ghr_pa_requests_rec.person_id is not null then
1443 l_employee_number := ghr_pa_requests_pkg2.get_employee_number
1444 (p_person_id => l_ghr_pa_requests_rec.person_id,
1445 p_effective_date => l_ghr_pa_requests_rec.effective_date);
1446 End if;
1447
1448 l_ghr_pa_requests_rec.employee_last_name := l_last_name;
1449
1450 hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
1451 --
1452 -- 2.1) Do not include PAR's for a non US Citizen in a foreign country
1453 IF non_us_citizen_and_foreign_ds (p_citizenship => l_ghr_pa_requests_rec.citizenship
1454 ,p_duty_station_code => l_ghr_pa_requests_rec.duty_station_code) THEN
1455 GOTO end_par_loop; -- loop for the next one!
1456 END IF;
1457 --
1458 hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
1459 --
1460 -- Bug 714944 -- Added exclusion of NAF:
1461 IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
1462 ,l_ghr_pa_requests_rec.from_position_id)
1463 ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
1464 GOTO end_par_loop; -- loop for the next one!
1465 END IF;
1466
1467 -- Obtain Retained Grade information
1468
1469 BEGIN
1470 l_retained_pay_plan := NULL;
1471 l_retained_grade_or_level := NULL;
1472 l_retained_step_or_rate := NULL;
1473 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
1474 p_person_id => l_ghr_pa_requests_rec.person_id,
1475 p_effective_date => l_ghr_pa_requests_rec.effective_date
1476 );
1477
1478 l_retained_pay_plan := l_retained_grade_rec.pay_plan;
1479 l_retained_grade_or_level := l_retained_grade_rec.grade_or_level;
1480 l_retained_step_or_rate := l_retained_grade_rec.step_or_rate;
1481 EXCEPTION
1482 WHEN ghr_pay_calc.pay_calc_message THEN
1483 NULL;
1484 END;
1485
1486 -- Emptying GHR_CPDF_TEMP_REC... Added for bug# 1375342
1487 l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
1488
1489
1490 --
1491 -- 2.2) Add a cutomer hook to determine whether or not to include in report or not!!!!
1492 -- This maybe particuarly useful for excluding Non appropriated fund personnel (NAF) as currently
1493 -- we do not hold this infoamtion about a person or position, but apparently DoD hold it
1494 -- in the position kff
1495 --
1496 --
1497 -- 3) Now we have decided to keep this populate the ghr_cpdf_temp record group:
1498 -- First with all the information the PAR table itself and then go and get any more information needed
1499 -- If we are just doing a count, we do not need to do the second bit!
1500 --
1501 -- 3.1) Get all info from PAR table itself
1502 --
1503 hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
1504 --
1505 l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
1506 l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
1507
1508 IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
1509 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
1510 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
1511 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
1512 (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
1513 IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
1514 l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
1515 l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
1516 ELSE
1517 l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
1518 l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
1519 END IF;
1520 END IF;
1521
1522 l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
1523 l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
1524 l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
1525
1526 -- IF Cancellation THEN no more data elements are needed. Bug# 1375323
1527 -- Insert_row in GHR_CPDF_TEMP, and continue in the LOOP for the next PAR row.
1528 IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
1529 insert_row(l_ghr_cpdf_temp_rec);
1530 l_records_found:=TRUE;
1531 GOTO end_par_loop; -- loop for the next one!
1532 END IF;
1533
1534 -- Obtain Family Code
1535 l_noa_family_code := l_ghr_pa_requests_rec.noa_family_code;
1536 IF l_noa_family_code = 'CORRECT' THEN
1537 -- Bug#2789704 Added Exception Handling
1538 BEGIN
1539 /*GHR_PROCESS_SF52.get_family_code(l_ghr_pa_requests_rec.second_noa_id,
1540 l_noa_family_code);*/
1541
1542 --Bug # 7507154 added this call to get the family code based on Effective date
1543 l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
1544 (l_ghr_pa_requests_rec.second_noa_id,
1545 l_ghr_pa_requests_rec.effective_date);
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 --Bug # 9329643 Modified SSN to Emp No
1549 l_message_name := 'get_family_code';
1550 l_log_text := 'Error in getting family code for pa_request_id: '||
1551 l_ghr_pa_requests_rec.pa_request_id ||
1552 ' ; Emp No/employee last name' ||
1553 l_employee_number ||' / '||
1554 l_ghr_pa_Requests_rec.employee_last_name ||
1555 ' ; first NOAC/Second NOAC: '||
1556 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1557 l_ghr_pa_requests_rec.second_noa_code ||
1558 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1559
1560 Raise CPDF_DYNRPT_ERROR;
1561 END;
1562 -- Bug#2789704 Added Exception Handling
1563 END IF;
1564
1565 -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
1566 l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
1567 l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
1568 l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
1569 l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
1570 l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
1571 l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
1572 l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
1573 l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
1574
1575 -- IF l_noa_family_code = 'AWARD' THEN bug#5328177
1576 IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
1577 l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
1578 l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
1579 l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
1580 l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
1581 l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
1582 l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
1583 l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
1584 END IF;
1585
1586 l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
1587 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
1588 l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
1589 l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
1590 l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
1591 l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
1592
1593 IF l_noa_family_code <> 'AWARD' THEN
1594 -- Added following 'IF' according to bug# 1375333
1595 IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
1596 l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
1597 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
1598 (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
1599 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
1600 )
1601 ) THEN
1602 l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
1603 ELSE
1604 l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
1605 END IF;
1606 END IF;
1607
1608 -- Added following 'IF' according to bug# 1375333
1609 IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
1610 l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
1611 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
1612 (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
1613 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
1614 )
1615 ) THEN
1616 l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
1617 --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
1618 -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
1619 ELSE
1620 l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
1621 END IF;
1622 l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
1623 l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
1624 l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
1625 l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
1626 -- Do the formating here and put the 'whole' name to be reported in just last name column!
1627 l_ghr_cpdf_temp_rec.employee_last_name := format_name(l_ghr_pa_requests_rec.employee_first_name
1628 ,l_ghr_pa_requests_rec.employee_last_name
1629 ,l_ghr_pa_requests_rec.employee_middle_names);
1630
1631 -- Added IF for bug# 1375342
1632 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
1633 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
1634 (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
1635 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '2%'
1636 )
1637 ) THEN
1638 l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
1639 l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
1640 l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
1641 l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
1642 l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
1643 l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
1644 END IF;
1645
1646 IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
1647 p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
1648
1649 --7507154 ADDED incentive family
1650 IF l_noa_family_code <> 'GHR_INCENTIVE' then
1651 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
1652 l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
1653 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
1654 (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
1655 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
1656 )
1657 ) THEN
1658 IF (get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW' OR
1659 --9862674 pay plans starting with N or D need to have locality adjustment.
1660 (substr(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan),1,1) IN ('N','D'))) THEN
1661 l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
1662 ELSE
1663 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
1664 END IF;
1665 ELSE
1666 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
1667 END IF;
1668 END IF;
1669
1670
1671
1672 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
1673 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
1674 (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
1675 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
1676 )
1677 ) AND
1678 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
1679 (l_ghr_pa_requests_rec.first_noa_code = '002' OR
1680 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
1681 )
1682 ) THEN
1683 IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
1684 l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
1685 ELSE
1686 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
1687 END IF;
1688 ELSE
1689 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
1690 END IF;
1691 ELSE --4163587 Loc pay is not reported for prior loc pay
1692 -- NO NEED TO MAKE PRIOR LOC PAY ADJ NULL, as we are checking for current DS not prior DS
1693 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
1694 END IF;
1695
1696
1697 l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
1698 l_ghr_cpdf_temp_rec.to_supervisory_differential:= l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
1699 l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
1700
1701 IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug #5328177
1702 IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
1703 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
1704 l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
1705 IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
1706 -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
1707 IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
1708 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
1709 l_ghr_pa_requests_rec.second_noa_code = '818') THEN
1710 -- Bug#2789704 Added Exception Handling
1711
1712 BEGIN
1713 ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
1714 ,p_input_value_name => 'Amount'
1715 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
1716 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
1717 ,p_value => l_ghr_cpdf_temp_rec.award_amount
1718 ,p_multiple_error_flag => l_multi_error_flag);
1719
1720 -- Added the if condition for Bug#5564750
1721 IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
1722 l_ghr_cpdf_temp_rec.award_amount := 0;
1723 END IF;
1724
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 --Bug # 9329643 Modified SSN to Emp No
1728 l_message_name := 'retrieve_element_entry_value';
1729 l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
1730 l_ghr_pa_requests_rec.pa_request_id ||
1731 ' ; Emp No/employee last name' ||
1732 l_employee_number ||' / '||
1733 l_ghr_pa_Requests_rec.employee_last_name ||
1734 ' ; first NOAC/Second NOAC: '||
1735 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1736 l_ghr_pa_requests_rec.second_noa_code ||
1737 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1738
1739 Raise CPDF_DYNRPT_ERROR;
1740 END;
1741
1742 -- Bug#2789704 Added Exception Handling
1743 ELSE
1744 -- Bug#2789704 Added Exception Handling
1745 BEGIN
1746 ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
1747 ,p_input_value_name => 'Amount'
1748 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
1749 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
1750 ,p_value => l_ghr_cpdf_temp_rec.award_amount
1751 ,p_multiple_error_flag => l_multi_error_flag);
1752
1753 -- Added the if condition for Bug#5564750
1754 IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
1755 l_ghr_cpdf_temp_rec.award_amount := 0;
1756 END IF;
1757
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 --Bug # 9329643 Modified SSN to Emp No
1761 l_message_name := 'retrieve_element_entry_value';
1762 l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
1763 l_ghr_pa_requests_rec.pa_request_id ||
1764 ' ; Emp No/employee last name' ||
1765 l_employee_number ||' / '||
1766 l_ghr_pa_Requests_rec.employee_last_name ||
1767 ' ; first NOAC/Second NOAC: '||
1768 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1769 l_ghr_pa_requests_rec.second_noa_code ||
1770 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1771
1772 Raise CPDF_DYNRPT_ERROR;
1773 END;
1774 -- Bug#2789704 Added Exception Handling
1775 END IF;
1776 END IF;
1777 ELSE
1778 IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
1779 -- Bug#2789704 Added Exception Handling
1780 BEGIN
1781 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
1782 ,p_input_value_name => 'Amount'
1783 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
1784 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
1785 ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
1786 ,p_multiple_error_flag => l_multi_error_flag);
1787 EXCEPTION
1788 WHEN OTHERS THEN
1789 --Bug # 9329643 Modified SSN to Emp No
1790 l_message_name := 'retrieve_element_entry_value';
1791 l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
1792 l_ghr_pa_requests_rec.pa_request_id ||
1793 ' ; Emp No/employee last name' ||
1794 l_employee_number ||' / '||
1795 l_ghr_pa_Requests_rec.employee_last_name ||
1796 ' ; first NOAC/Second NOAC: '||
1797 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1798 l_ghr_pa_requests_rec.second_noa_code ||
1799 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1800
1801 Raise CPDF_DYNRPT_ERROR;
1802 END;
1803 -- Bug#2789704 Added Exception Handling
1804
1805 END IF;
1806 IF l_ghr_cpdf_temp_rec.to_retention_allowance IS NULL THEN
1807 -- Bug#2789704 Added Exception Handling
1808 BEGIN
1809 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
1810 ,p_input_value_name => 'Amount'
1811 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
1812 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
1813 ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
1814 ,p_multiple_error_flag => l_multi_error_flag);
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 --Bug # 9329643 Modified SSN to Emp No
1818 l_message_name := 'retrieve_element_entry_value';
1819 l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
1820 l_ghr_pa_requests_rec.pa_request_id ||
1821 ' ; Emp No/employee last name' ||
1822 l_employee_number ||' / '||
1823 l_ghr_pa_Requests_rec.employee_last_name ||
1824 ' ; first NOAC/Second NOAC: '||
1825 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1826 l_ghr_pa_requests_rec.second_noa_code ||
1827 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1828
1829 Raise CPDF_DYNRPT_ERROR;
1830 END;
1831 -- Bug#2789704 Added Exception Handling
1832 END IF;
1833 END IF;
1834 END IF;
1835
1836 -- Changes for education
1837 -- Fix for 3958881 Madhuri
1838 ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
1839 p_effective_date => l_ghr_pa_requests_rec.effective_date,
1840 p_education_level => l_ghr_cpdf_temp_rec.education_level,
1841 p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
1842 p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
1843 -- End changes for education Commented below code
1844
1845 /* l_ghr_cpdf_temp_rec.education_level := l_ghr_pa_requests_rec.education_level;
1846 -- academic_discipline is refered to as 'Instructional Program' in the dynamics report
1847 l_ghr_cpdf_temp_rec.academic_discipline := l_ghr_pa_requests_rec.academic_discipline;
1848 l_ghr_cpdf_temp_rec.year_degree_attained := l_ghr_pa_requests_rec.year_degree_attained;
1849 -- */
1850
1851 -- Not worth getting any more detials if only counting!
1852 IF not p_count_only THEN
1853 --
1854 -- 3.2) Get Orgnaizational Component (Otherwise refered to as Org Structure ID)
1855 -- Since this appears to be required for all NOA's reported in the dynamic report
1856 -- it must come from to_position if there, if not from the from_position
1857 --
1858 -- Bug#2789704 Added Exception Handling
1859 BEGIN
1860 get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
1861 ,l_ghr_pa_requests_rec.from_position_id)
1862 ,l_ghr_pa_requests_rec.effective_date
1863 ,l_ghr_cpdf_temp_rec.organizational_component);
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 --Bug # 9329643 Modified SSN to Emp No
1867 l_message_name := 'get_org_comp';
1868 l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
1869 l_ghr_pa_requests_rec.pa_request_id ||
1870 ' ; Emp No/employee last name' ||
1871 l_employee_number ||' / '||
1872 l_ghr_pa_Requests_rec.employee_last_name ||
1873 ' ; first NOAC/Second NOAC: '||
1874 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1875 l_ghr_pa_requests_rec.second_noa_code ||
1876 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1877
1878 Raise CPDF_DYNRPT_ERROR;
1879 END;
1880 -- Bug#2789704 Added Exception Handling
1881 --
1882 -- 3.3) Get Sex
1883 -- Bug#2789704 Added Exception Handling
1884 BEGIN
1885 get_sex (l_ghr_pa_requests_rec.person_id
1886 ,l_ghr_pa_requests_rec.effective_date
1887 ,l_ghr_cpdf_temp_rec.sex);
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 --Bug # 9329643 Modified SSN to Emp No
1891 l_message_name := 'get_sex';
1892 l_log_text := 'Error in fetching SEX for pa_request_id: '||
1893 l_ghr_pa_requests_rec.pa_request_id ||
1894 ' ; Emp No/employee last name' ||
1895 l_employee_number ||' / '||
1896 l_ghr_pa_Requests_rec.employee_last_name ||
1897 ' ; first NOAC/Second NOAC: '||
1898 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1899 l_ghr_pa_requests_rec.second_noa_code ||
1900 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1901
1902 Raise CPDF_DYNRPT_ERROR;
1903 END;
1904 -- Bug#2789704 Added Exception Handling
1905 --
1906 -- 3.4) Get person SIT - 'US Fed Perf Appraisal'
1907 --
1908 -- Bug#2789704 Added Exception Handling
1909 BEGIN
1910 get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
1911 ,l_ghr_pa_requests_rec.effective_date
1912 ,l_ghr_cpdf_temp_rec.rating_of_record_level
1913 ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
1914 ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends); -- format in report
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 --Bug # 9329643 Modified SSN to Emp No
1918 l_message_name := 'get_per_sit_perf_apprisal';
1919 l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
1920 to_char(l_ghr_pa_requests_rec.pa_request_id) ||
1921 ' ; Emp No/employee last name' ||
1922 l_employee_number ||' / '||
1923 l_ghr_pa_Requests_rec.employee_last_name ||
1924 ' ; first NOAC/Second NOAC: '||
1925 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1926 l_ghr_pa_requests_rec.second_noa_code ||
1927 ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1928 Raise CPDF_DYNRPT_ERROR;
1929
1930 END;
1931 -- Bug#2789704 Added Exception Handling
1932 --
1933 -- 3.5) Get PAR Extra Info Noa specific
1934 --
1935 -- Bug#2789704 Added Exception Handling
1936 BEGIN
1937 get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
1938 ,l_ghr_pa_requests_rec.first_noa_id
1939 ,l_ghr_pa_requests_rec.second_noa_id
1940 ,l_ghr_pa_requests_rec.noa_family_code
1941 ,l_ghr_pa_requests_rec.person_id
1942 ,l_ghr_pa_requests_rec.effective_date
1943 ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
1944 ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
1945 ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
1946 ,l_ghr_cpdf_temp_rec.race_national_origin
1947 ,l_ghr_cpdf_temp_rec.handicap_code
1948 ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
1949 ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
1950 ,l_ghr_cpdf_temp_rec.race_ethnic_info); -- -- Bug 4724337 Race or National Origin changes
1951 EXCEPTION
1952 WHEN OTHERS THEN
1953 l_message_name := 'get_par_ei_noac';
1954 --Bug # 9329643 Modified SSN to Emp No
1955 l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
1956 l_ghr_pa_requests_rec.pa_request_id ||
1957 ' ; Emp No/employee last name' ||
1958 l_employee_number ||' / '||
1959 l_ghr_pa_Requests_rec.employee_last_name ||
1960 ' ; first NOAC/Second NOAC: '||
1961 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1962 l_ghr_pa_requests_rec.second_noa_code ||
1963 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1964
1965 Raise CPDF_DYNRPT_ERROR;
1966 END;
1967 -- Bug#2789704 Added Exception Handling
1968
1969 -- Bug# 1375342
1970 IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
1971 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
1972 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
1973 l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
1974 l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
1975 -- for bug 3327389
1976 END IF;
1977
1978 -- get current appointment auth codes.
1979 -- Bug#2789704 Added Exception Handling
1980 BEGIN
1981 ghr_sf52_pre_update.get_auth_codes
1982 (p_pa_req_rec => l_ghr_pa_requests_rec
1983 ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
1984 ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 l_message_name := 'get_auth_codes';
1988 --Bug # 9329643 Modified SSN to Emp No
1989 l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
1990 l_ghr_pa_requests_rec.pa_request_id ||
1991 ' ; Emp No/employee last name' ||
1992 l_employee_number ||' / '||
1993 l_ghr_pa_Requests_rec.employee_last_name ||
1994 ' ; first NOAC/Second NOAC: '||
1995 l_ghr_pa_requests_rec.first_noa_code || ' / '||
1996 l_ghr_pa_requests_rec.second_noa_code ||
1997 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1998
1999 Raise CPDF_DYNRPT_ERROR;
2000 END;
2001 -- Bug#2789704 Added Exception Handling
2002 --
2003 -- 3.6) Get PRIOR Work Schedule and Pay Rate Determinant
2004 --
2005 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2006 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2007 (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
2008 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '2%'))
2009 THEN
2010 -- Bug#2789704 Added Exception Handling
2011 BEGIN
2012 get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
2013 ,l_ghr_pa_requests_rec.altered_pa_request_id
2014 ,l_ghr_pa_requests_rec.first_noa_id
2015 ,l_ghr_pa_requests_rec.second_noa_id
2016 ,l_ghr_pa_requests_rec.person_id
2017 ,l_ghr_pa_requests_rec.employee_assignment_id
2018 ,l_ghr_pa_requests_rec.from_position_id
2019 ,l_ghr_pa_requests_rec.effective_date
2020 ,l_ghr_pa_requests_rec.status
2021 ,l_ghr_cpdf_temp_rec.from_work_schedule
2022 ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
2023 ,l_ghr_cpdf_temp_rec.from_duty_station_code);
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 l_message_name := 'get_prior_ws_prd_ds';
2027 --Bug # 9329643 Modified SSN to Emp No
2028 l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
2029 l_ghr_pa_requests_rec.pa_request_id ||
2030 ' ; Emp No/employee last name' ||
2031 l_employee_number ||' / '||
2032 l_ghr_pa_Requests_rec.employee_last_name ||
2033 ' ; first NOAC/Second NOAC: '||
2034 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2035 l_ghr_pa_requests_rec.second_noa_code ||
2036 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2037
2038 Raise CPDF_DYNRPT_ERROR;
2039 END;
2040
2041 --bug# 5328177 IF l_noa_family_code = 'AWARD' THEN
2042 IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
2043
2044 l_ghr_cpdf_temp_rec.to_pay_rate_determinant := l_ghr_cpdf_temp_rec.from_pay_rate_determinant;
2045 END IF;
2046 -- Added IF Condition to fix bug#3231946
2047 IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
2048 p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
2049 AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
2050 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
2051 END IF;
2052
2053 END IF;
2054 --
2055 -- 3.7) Get prior ssn if it is being corrected.
2056 --
2057 -- Bug#2789704 Added Exception Handling
2058 BEGIN
2059 get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
2060 ,l_ghr_pa_requests_rec.employee_national_identifier
2061 ,l_ghr_pa_requests_rec.noa_family_code
2062 ,l_ghr_cpdf_temp_rec.from_national_identifier);
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 l_message_name := 'get_prev_ssn';
2066 --Bug # 9329643 Modified SSN to Emp No
2067 l_log_text := 'Error in fetching SSN for pa_request_id: '||
2068 l_ghr_pa_requests_rec.pa_request_id ||
2069 ' ; Emp No/employee last name' ||
2070 l_employee_number ||' / '||
2071 l_ghr_pa_Requests_rec.employee_last_name ||
2072 ' ; first NOAC/Second NOAC: '||
2073 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2074 l_ghr_pa_requests_rec.second_noa_code ||
2075 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2076
2077 Raise CPDF_DYNRPT_ERROR;
2078 END;
2079
2080 END IF; -- end of popluation of full record if not count_only
2081 --
2082 -- adding code for 817 NOAC
2083 IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
2084 l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
2085 l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2086 l_ghr_cpdf_temp_rec.to_national_identifier
2087 := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2088 l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
2089 l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
2090 l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
2091 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2092 l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount;
2093 -- GOTO end_par_loop;
2094 END IF;
2095
2096 ---
2097 -- EHRI changes
2098 --
2099 IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
2100 AND
2101 (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
2102 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
2103 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
2104 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
2105 ) -- for 002
2106 OR
2107 (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
2108 l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
2109 l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
2110 l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
2111 )
2112 )
2113 THEN
2114
2115 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2116 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
2117 (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
2118 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
2119 )
2120 ) AND
2121 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
2122 (l_ghr_pa_requests_rec.first_noa_code = '002' OR
2123 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
2124 )
2125 ) THEN
2126 l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
2127 ELSE
2128 l_ghr_cpdf_temp_rec.position_title := NULL;
2129 END IF;
2130
2131 -- Bug#5328177 Added NOAC 827 in the NOAC list.
2132 -- Bug # 8510442 Added 885 into the list to display award amount for 885 action
2133 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2134 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825','827',
2135 '840','841','842','843','844','845','846','847','848','849','878','879','885')
2136 )
2137 OR
2138 l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','818','819','825','827',
2139 '840','841','842','843','844','845','846','847','848','849','878','879','885')
2140 )
2141 THEN
2142
2143 -- Bug#3941541,5168358 Separation Incentive Changes.
2144 -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
2145 -- Otherwise, it is processed as Incentive.
2146 IF ( l_ghr_pa_requests_rec.first_noa_code IN ('825','815','816') OR
2147 l_ghr_pa_requests_rec.second_noa_code IN ('825','815','816')) AND
2148 l_ghr_pa_requests_rec.award_amount IS NULL THEN
2149 l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2150 l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2151 END IF;
2152 -- End of Bug#3941541,5168358
2153
2154 IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
2155 l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
2156 END IF;
2157 IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
2158 l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
2159 END IF;
2160 IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
2161 l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
2162 END IF;
2163 -- Bug#5328177 Added the following IF Condition
2164 IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2165 l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2166 l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2167 END IF;
2168
2169
2170 END IF;
2171
2172 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code='817')
2173 OR
2174 l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
2175 l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
2176 l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
2177 l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
2178 -- Added format_name_ehri for EHRI changes.
2179 FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id)
2180 LOOP
2181 -- Bug# 4648811getting the suffix from the Employee lastname
2182 get_suffix_lname(per_det.last_name,
2183 l_ghr_pa_requests_rec.effective_date,
2184 l_suffix,
2185 l_last_name);
2186 --End Bug# 4648811
2187 l_ghr_cpdf_temp_rec.name_title := l_suffix;
2188 END LOOP;
2189
2190 FOR scd_dates IN cur_scd_dates(l_ghr_pa_requests_rec.pa_request_id)
2191 LOOP
2192 l_ghr_cpdf_temp_rec.SCD_rif := fnd_date.canonical_to_date(scd_dates.rif);
2193 l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
2194 -- Added date conversion for bug#3808473-EHRI reports
2195 END LOOP;
2196
2197 IF (l_ghr_cpdf_temp_rec.SCD_rif IS NULL
2198 and l_ghr_cpdf_temp_rec.SCD_retirement IS NULL) THEN
2199
2200 BEGIN
2201 ghr_history_fetch.fetch_peopleei
2202 (p_person_id => l_ghr_pa_requests_rec.person_id,
2203 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
2204 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
2205 p_per_ei_data => ll_per_ei_data
2206 );
2207
2208 l_ghr_cpdf_temp_rec.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
2209 l_ghr_cpdf_temp_rec.SCD_retirement:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information7);
2210
2211 EXCEPTION
2212 WHEN OTHERS THEN
2213 l_message_name := 'fetch_peopleei';
2214 --Bug # 9329643 Modified SSN to Emp No
2215 l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
2216 l_ghr_pa_requests_rec.pa_request_id ||
2217 ' ; Emp No/employee last name' ||
2218 l_employee_number ||' / '||
2219 l_ghr_pa_Requests_rec.employee_last_name ||
2220 ' ; first NOAC/Second NOAC: '||
2221 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2222 l_ghr_pa_requests_rec.second_noa_code ||
2223 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2224
2225 Raise CPDF_DYNRPT_ERROR;
2226 END;
2227 END IF;
2228
2229 ELSE -- if NOAC is 817
2230 l_ghr_cpdf_temp_rec.employee_last_name := NULL;
2231 l_ghr_cpdf_temp_rec.employee_first_name := NULL;
2232 l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
2233 l_ghr_cpdf_temp_rec.name_title := NULL;
2234 l_ghr_cpdf_temp_rec.SCD_rif := NULL;
2235 l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
2236 END IF; -- not in 817
2237 END IF;
2238 -- EHRI changes END
2239 --
2240
2241 --Pradeep start of Bug 3953500
2242 /* In case of 825 instead of taking TO side values From side values are taken as
2243 to side values are not populated.
2244 in future in case to side values are populated consider only the toside values.
2245 */
2246 IF l_ghr_pa_requests_rec.first_noa_code = '825'
2247 OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
2248 l_ghr_pa_requests_rec.first_noa_code = '825' ) THEN
2249 l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2250 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2251 l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2252 l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2253 l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2254 l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2255
2256 l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
2257 l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
2258 END IF;
2259
2260 IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
2261
2262 BEGIN
2263 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
2264 ,p_input_value_name => 'Amount'
2265 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
2266 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
2267 ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
2268 ,p_multiple_error_flag => l_multi_error_flag);
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 --Bug # 9329643 Modified SSN to Emp No
2272 l_message_name := 'retrieve_element_entry_value';
2273 l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
2274 l_ghr_pa_requests_rec.pa_request_id ||
2275 ' ; Emp No/employee last name' ||
2276 l_employee_number ||' / '||
2277 l_ghr_pa_Requests_rec.employee_last_name ||
2278 ' ; first NOAC/Second NOAC: '||
2279 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2280 l_ghr_pa_requests_rec.second_noa_code ||
2281 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2282
2283 Raise CPDF_DYNRPT_ERROR;
2284 END;
2285
2286 END IF;
2287 IF l_ghr_cpdf_temp_rec.to_retention_allowance IS NULL THEN
2288
2289 BEGIN
2290 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
2291 ,p_input_value_name => 'Amount'
2292 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
2293 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
2294 ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
2295 ,p_multiple_error_flag => l_multi_error_flag);
2296 EXCEPTION
2297 WHEN OTHERS THEN
2298 --Bug # 9329643 Modified SSN to Emp No
2299 l_message_name := 'retrieve_element_entry_value';
2300 l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
2301 l_ghr_pa_requests_rec.pa_request_id ||
2302 ' ; Emp No/employee last name' ||
2303 l_employee_number ||' / '||
2304 l_ghr_pa_Requests_rec.employee_last_name ||
2305 ' ; first NOAC/Second NOAC: '||
2306 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2307 l_ghr_pa_requests_rec.second_noa_code ||
2308 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2309
2310 Raise CPDF_DYNRPT_ERROR;
2311 END;
2312
2313 END IF;
2314 --Pradeep end of Bug 3953500
2315
2316 -- 3327389 Bug fix start
2317 -- CPDF Reporting changes to include Creditable Military Service, Frozen Service and Prev Retirement Coverage
2318 -- including the NOACS 800 and 782 inspite they are optional for reporting
2319 -- as they will be anyways filtered under exclude_noacs
2320 BEGIN
2321 /* IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2322 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('280','292','293'))
2323 OR l_ghr_pa_requests_rec.first_noa_code in ('280','292','293')
2324 ) THEN
2325 ghr_history_fetch.fetch_peopleei
2326 (p_person_id => l_ghr_pa_requests_rec.person_id,
2327 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
2328 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
2329 p_per_ei_data => ll_per_ei_data
2330 );
2331 l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
2332 -- Cerditable mil serv, frozen serv are already picked up from get_PAR_EI_noac procedure
2333 -- information5 for Frozen service
2334 ll_per_ei_data:=NULL;
2335
2336 END IF; */
2337 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2338 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
2339 '850','855','867','868','891','892','893','895','899'))
2340 OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
2341 '850','855','867','868','891','892','893','895','899')
2342 ) THEN
2343
2344 ghr_history_fetch.fetch_peopleei
2345 (p_person_id => l_ghr_pa_requests_rec.person_id,
2346 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
2347 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
2348 p_per_ei_data => ll_per_ei_data
2349 );
2350
2351 l_ghr_cpdf_temp_rec.creditable_military_service:= SUBSTR(ll_per_ei_data.pei_information5,1,4);
2352 ll_per_ei_data :=NULL;
2353
2354 ghr_history_fetch.fetch_peopleei
2355 (p_person_id => l_ghr_pa_requests_rec.person_id,
2356 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
2357 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
2358 p_per_ei_data => ll_per_ei_data
2359 );
2360 l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
2361 l_ghr_cpdf_temp_rec.Frozen_service:= SUBSTR(ll_per_ei_data.pei_information5,1,4);
2362
2363 ll_per_ei_data:=NULL;
2364 END IF;
2365
2366 -- If Ethnicity is reported, RNO should be null
2367 IF l_ghr_cpdf_temp_rec.race_ethnic_info IS NOT NULL THEN
2368 l_ghr_cpdf_temp_rec.race_national_origin := NULL;
2369 END IF;
2370
2371
2372 -- Bug 4542476
2373 l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
2374 p_effective_date => l_ghr_pa_requests_rec.effective_date);
2375 l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
2376
2377 -- Bug# 8939586 added the bug fix 5011003 which is fixed for EHRI Dynamics report
2378 IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
2379 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
2380 ELSE
2381 IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
2382 AND l_equiv_plan = 'GS' THEN
2383 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
2384 ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
2385 AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
2386 l_ghr_cpdf_temp_rec.to_locality_adj := 0;
2387 ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
2388 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
2389 END IF;
2390 END IF;
2391
2392 -- For Prior locality pay
2393 -- Bug 8939586
2394 IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
2395 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
2396 ELSE
2397 IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
2398 AND l_equiv_plan = 'GS' THEN
2399 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
2400 ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
2401 AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
2402 l_ghr_cpdf_temp_rec.from_locality_adj := 0;
2403 ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
2404 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
2405 END IF;
2406 -- End Bug 4542476
2407 END IF;
2408
2409
2410
2411
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 --Bug # 9329643 Modified SSN to Emp No
2415 l_message_name := 'fetch_peopleei';
2416 l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
2417 l_ghr_pa_requests_rec.pa_request_id ||
2418 ' ; Emp No/employee last name' ||
2419 l_employee_number ||' / '||
2420 l_ghr_pa_Requests_rec.employee_last_name ||
2421 ' ; first NOAC/Second NOAC: '||
2422 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2423 l_ghr_pa_requests_rec.second_noa_code ||
2424 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2425 END;
2426 -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
2427 -- 3327389 Bug fix end
2428 insert_row(l_ghr_cpdf_temp_rec);
2429 l_records_found:=TRUE;
2430 --
2431 <<end_par_loop>>
2432 NULL;
2433 EXCEPTION
2434 WHEN CPDF_DYNRPT_ERROR THEN
2435 hr_utility.set_location('Inside CPDF_DYNRPT_ERROR exception ',30);
2436 ghr_mto_int.log_message(p_procedure => l_message_name,
2437 p_message => l_log_text
2438 );
2439 COMMIT;
2440 WHEN OTHERS THEN
2441 hr_utility.set_location('Inside WHEN_OTHERS exception ',40);
2442 --Bug # 9329643 Modified SSN to Emp No
2443 l_message_name := 'Unhandled Error';
2444 l_log_text := 'Unhandled Error for pa_request_id: '||
2445 l_ghr_pa_requests_rec.pa_request_id ||
2446 ' ; Emp No/employee last name' ||
2447 l_employee_number ||' / '||
2448 l_ghr_pa_Requests_rec.employee_last_name ||
2449 ' ; first NOAC/Second NOAC: '||
2450 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2451 l_ghr_pa_requests_rec.second_noa_code ||
2452 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2453 ghr_mto_int.log_message(p_procedure => l_message_name,
2454 p_message => l_log_text
2455 );
2456 COMMIT;
2457
2458 END;
2459 END LOOP; -- end of check for single flag or dual flag
2460 END LOOP;
2461
2462 IF NOT l_records_found THEN
2463 l_message_name:='RECORDS_NOT_FOUND';
2464 l_log_text:= 'No Records found for the period '||p_start_Date||' - '||p_end_date;
2465 ghr_mto_int.log_message(p_procedure => l_message_name,
2466 p_message => l_log_text
2467 );
2468
2469 l_mesgbuff1:='No Records found for the period '||p_start_Date||' - '||p_end_date;
2470 fnd_file.put(fnd_file.log,l_mesgbuff1);
2471 fnd_file.new_line(fnd_file.log);
2472 END IF;
2473
2474 ELSE -- DATES are not proper
2475 l_message_name:= 'CHECK_REPORT_FROM_TO_DATES';
2476 l_log_text:='The Report To Date: '||p_end_date||' is less than the Report From Date: '||p_start_date||
2477 '. Please enter a value for Report To Date greater than or equal to the Report Start Date';
2478
2479 ghr_mto_int.log_message(p_procedure => l_message_name,
2480 p_message => l_log_text
2481 );
2482 END IF;
2483
2484 --
2485 hr_utility.set_location('Leaving:'||l_proc, 99);
2486 -- hr_utility.trace_off;
2487 --
2488 END populate_ghr_cpdf_temp;
2489 --
2490 END ghr_cpdf_dynrpt;
[Home] [Help]