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