[Home] [Help]
PACKAGE BODY: APPS.GHR_EHRI_DYNRPT
Source
1 PACKAGE BODY GHR_EHRI_DYNRPT AS
2 /* $Header: ghrehrid.pkb 120.31.12010000.2 2008/08/05 15:11:48 ubhat ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := 'ghr_ehri_dynrpt.'; -- Global package name
9 g_eff_seq_no number := 1;
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 -- BUG # 7229419 commented the NOAC's related to 8XX and 7XX as these
75 -- need to be reported in EHRI Dynamics Report
76 IF (l_noac BETWEEN '900' and '999')
77 /*or
78 (l_noac IN ( '850','855','750','782','800','805','806','880','881','882','883'))*/
79 THEN
80 RETURN ('TRUE');
81 ELSE
82 RETURN ('FALSE');
83 END IF;
84
85 END exclude_noac;
86 --
87 ---------------------------------------------------------------------------------------------
88 -- This function returns TRUE if the info passed in means it is a non us citizen
89 -- AND a foreign DS
90 ---------------------------------------------------------------------------------------------
91 FUNCTION non_us_citizen_and_foreign_ds (p_citizenship IN VARCHAR2
92 ,p_duty_station_code IN VARCHAR2)
93 RETURN BOOLEAN IS
94 l_ds_2chars VARCHAR2(2);
95 BEGIN
96 -- The definition of non us citizen is citizenship does not equal 1
97 IF p_citizenship <> 1 THEN
98 -- The deifnition of a 'foreign' duty staion is:
99 -- If the first 2 positions of the duty station are alphabetic this means it is either
100 -- 1) foreign country
101 -- or 2) US possesion
102 -- or 3) US administritive area
103 -- Since all we what is 1)'s we exclude 2) and 3)'s by the list GQ,RQ etc...
104 l_ds_2chars := TRANSLATE(SUBSTR(p_duty_station_code,1,2),'0123456789','000000000');
105 IF l_ds_2chars <> '00'
106 AND l_ds_2chars NOT IN ('GQ','RQ','AQ','FM','JQ',
107 'CQ','MQ','RM','HQ','PS',
108 'BQ','WQ','VQ') THEN
109 RETURN(TRUE);
110 END IF;
111 END IF;
112 RETURN (FALSE);
113 END non_us_citizen_and_foreign_ds;
114 --
115 ---------------------------------------------------------------------------------------------
116 -- This function returns 'TRUE' if the for the position passed in on the given date
117 -- it is not an Appopriated fund
118 ---------------------------------------------------------------------------------------------
119 FUNCTION exclude_position (p_position_id IN NUMBER
120 ,p_effective_date IN DATE)
121 RETURN BOOLEAN IS
122 --
123 l_pos_ei_grp2_data per_position_extra_info%rowtype;
124 l_position_type VARCHAR2(150);
125 --
126 BEGIN
127 -- first get the Position Type on Position Group 2
128 ghr_history_fetch.fetch_positionei(
129 p_position_id => p_position_id,
130 p_information_type => 'GHR_US_POS_GRP2',
131 p_date_effective => p_effective_date,
132 p_pos_ei_data => l_pos_ei_grp2_data);
133 --
134 l_position_type := l_pos_ei_grp2_data.poei_information17;
135 --
136 IF NVL(l_position_type,'APPR') <> 'APPR' THEN
137 RETURN (TRUE);
138 ELSE
139 RETURN (FALSE);
140 END IF;
141 END exclude_position;
142 --
143 ---------------------------------------------------------------------------------------------
144 -- B) This section includes any getting of extra data procedures
145 ---------------------------------------------------------------------------------------------
146 --
147 ---------------------------------------------------------------------------------------------
148 -- This one gets org component (also refered to as org structure id) for a given position
149 ---------------------------------------------------------------------------------------------
150 PROCEDURE get_org_comp (p_position_id IN NUMBER
151 ,p_effective_date IN DATE
152 ,p_org_comp OUT NOCOPY VARCHAR2) IS
153 --
154 l_pos_ei_grp1_data per_position_extra_info%ROWTYPE;
155 BEGIN
156 ghr_history_fetch.fetch_positionei(p_position_id => p_position_id
157 ,p_information_type => 'GHR_US_POS_GRP1'
158 ,p_date_effective => p_effective_date
159 ,p_pos_ei_data => l_pos_ei_grp1_data);
160
161 p_org_comp := l_pos_ei_grp1_data.poei_information5;
162 EXCEPTION
163 WHEN OTHERS THEN
164 p_org_comp := NULL;
165 raise;
166 END get_org_comp;
167 --
168
169 --------------------------------------------------------------------------------------------
170 --- This one picks the appropriation code 1 for a position -- NEW EHRI CHANGES
171 --------------------------------------------------------------------------------------------
172 PROCEDURE get_appr_code (p_position_id IN NUMBER
173 ,p_effective_date IN DATE
174 ,p_appr_code OUT NOCOPY VARCHAR2) IS
175 --
176 l_pos_ei_grp1_data per_position_extra_info%ROWTYPE;
177 l_appr_code per_position_extra_info.POEI_INFORMATION13%TYPE;
178 BEGIN
179 ghr_history_fetch.fetch_positionei(p_position_id => p_position_id
180 ,p_information_type => 'GHR_US_POS_GRP2'
181 ,p_date_effective => p_effective_date
182 ,p_pos_ei_data => l_pos_ei_grp1_data);
183
184 l_appr_code := l_pos_ei_grp1_data.POEI_INFORMATION13;
185
186 --
187 p_appr_code := l_appr_code;
188 EXCEPTION
189 WHEN OTHERS THEN
190 p_appr_code := NULL;
191 raise;
192 END get_appr_code;
193 --
194 ---------------------------------------------------------------------------------------------
195 -- This one gets sex for a given person
196 ---------------------------------------------------------------------------------------------
197 PROCEDURE get_sex (p_person_id IN NUMBER
198 ,p_effective_date IN DATE
199 ,p_sex OUT NOCOPY VARCHAR2) IS
200 CURSOR cur_per_sex IS
201 SELECT per.sex
202 FROM per_all_people_f per
203 WHERE per.person_id = p_person_id
204 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date;
205 BEGIN
206 FOR cur_per_sex_rec IN cur_per_sex LOOP
207 p_sex := cur_per_sex_rec.sex;
208 END LOOP;
209 EXCEPTION
210 WHEN OTHERS THEN
211 p_sex := NULL;
212 raise;
213 END get_sex;
214 --
215 ---------------------------------------------------------------------------------------------
216 -- Returns the whole ghr_pa_request_extra_info (rei) record for a given info type
217 ---------------------------------------------------------------------------------------------
218 PROCEDURE get_PAR_EI (p_pa_request_id IN NUMBER
219 ,p_noa_family_code IN VARCHAR2
220 ,p_information_type IN VARCHAR2
221 ,p_rei_rec OUT NOCOPY ghr_pa_request_extra_info%ROWTYPE) IS
222 CURSOR c_par IS
223 SELECT par.pa_request_id
224 FROM ghr_pa_requests par
225 CONNECT BY par.pa_request_id = prior par.altered_pa_request_id
226 START WITH par.pa_request_id = p_pa_request_id;
227 --
228 CURSOR cur_rei (cp_pa_request_id IN NUMBER) IS
229 SELECT *
230 FROM ghr_pa_request_extra_info rei
231 WHERE rei.information_type = p_information_type
232 AND rei.pa_request_id = cp_pa_request_id;
233 --
234 l_rei_rec ghr_pa_request_extra_info%ROWTYPE;
235 BEGIN
236 -- This extra info is actually on all NOAC's
237 -- For corrections, need a different way to get the data form the original 52 being
238 -- corrected since we do not populate the data for a correction!
239 IF p_noa_family_code <> 'CORRECT' THEN
240 FOR cur_rei_rec IN cur_rei(p_pa_request_id) LOOP
241 p_rei_rec := cur_rei_rec;
242 END LOOP;
243 ELSE
244 -- loop round all the pa_requests, picking up anything that is blank
245 FOR c_par_rec IN c_par LOOP
246 FOR cur_rei_rec IN cur_rei(c_par_rec.pa_request_id) LOOP
247 IF l_rei_rec.rei_information1 IS NULL THEN
248 l_rei_rec.rei_information1 := cur_rei_rec.rei_information1;
249 END IF;
250 IF l_rei_rec.rei_information2 IS NULL THEN
251 l_rei_rec.rei_information2 := cur_rei_rec.rei_information2;
252 END IF;
253 IF l_rei_rec.rei_information3 IS NULL THEN
254 l_rei_rec.rei_information3 := cur_rei_rec.rei_information3;
255 END IF;
256 IF l_rei_rec.rei_information4 IS NULL THEN
257 l_rei_rec.rei_information4 := cur_rei_rec.rei_information4;
258 END IF;
259 IF l_rei_rec.rei_information5 IS NULL THEN
260 l_rei_rec.rei_information5 := cur_rei_rec.rei_information5;
261 END IF;
262 IF l_rei_rec.rei_information6 IS NULL THEN
263 l_rei_rec.rei_information6 := cur_rei_rec.rei_information6;
264 END IF;
265 IF l_rei_rec.rei_information7 IS NULL THEN
266 l_rei_rec.rei_information7 := cur_rei_rec.rei_information7;
267 END IF;
268 IF l_rei_rec.rei_information8 IS NULL THEN
269 l_rei_rec.rei_information8 := cur_rei_rec.rei_information8;
270 END IF;
271 IF l_rei_rec.rei_information9 IS NULL THEN
272 l_rei_rec.rei_information9 := cur_rei_rec.rei_information9;
273 END IF;
274 IF l_rei_rec.rei_information10 IS NULL THEN
275 l_rei_rec.rei_information10 := cur_rei_rec.rei_information10;
276 END IF;
277 IF l_rei_rec.rei_information11 IS NULL THEN
278 l_rei_rec.rei_information11 := cur_rei_rec.rei_information11;
279 END IF;
280 IF l_rei_rec.rei_information12 IS NULL THEN
281 l_rei_rec.rei_information12 := cur_rei_rec.rei_information12;
282 END IF;
283 IF l_rei_rec.rei_information13 IS NULL THEN
284 l_rei_rec.rei_information13 := cur_rei_rec.rei_information13;
285 END IF;
286 IF l_rei_rec.rei_information14 IS NULL THEN
287 l_rei_rec.rei_information14 := cur_rei_rec.rei_information14;
288 END IF;
289 IF l_rei_rec.rei_information15 IS NULL THEN
290 l_rei_rec.rei_information15 := cur_rei_rec.rei_information15;
291 END IF;
292 IF l_rei_rec.rei_information16 IS NULL THEN
293 l_rei_rec.rei_information16 := cur_rei_rec.rei_information16;
294 END IF;
295 IF l_rei_rec.rei_information17 IS NULL THEN
296 l_rei_rec.rei_information17 := cur_rei_rec.rei_information17;
297 END IF;
298 IF l_rei_rec.rei_information18 IS NULL THEN
299 l_rei_rec.rei_information18 := cur_rei_rec.rei_information18;
300 END IF;
301 IF l_rei_rec.rei_information19 IS NULL THEN
302 l_rei_rec.rei_information19 := cur_rei_rec.rei_information19;
303 END IF;
304 IF l_rei_rec.rei_information20 IS NULL THEN
305 l_rei_rec.rei_information20 := cur_rei_rec.rei_information20;
306 END IF;
307 IF l_rei_rec.rei_information21 IS NULL THEN
308 l_rei_rec.rei_information21 := cur_rei_rec.rei_information21;
309 END IF;
310 IF l_rei_rec.rei_information22 IS NULL THEN
311 l_rei_rec.rei_information22 := cur_rei_rec.rei_information22;
312 END IF;
313 IF l_rei_rec.rei_information23 IS NULL THEN
314 l_rei_rec.rei_information23 := cur_rei_rec.rei_information23;
315 END IF;
316 IF l_rei_rec.rei_information24 IS NULL THEN
317 l_rei_rec.rei_information24 := cur_rei_rec.rei_information24;
318 END IF;
319 IF l_rei_rec.rei_information25 IS NULL THEN
320 l_rei_rec.rei_information25 := cur_rei_rec.rei_information25;
321 END IF;
322 IF l_rei_rec.rei_information26 IS NULL THEN
323 l_rei_rec.rei_information26 := cur_rei_rec.rei_information26;
324 END IF;
325 IF l_rei_rec.rei_information27 IS NULL THEN
326 l_rei_rec.rei_information27 := cur_rei_rec.rei_information27;
327 END IF;
328 IF l_rei_rec.rei_information28 IS NULL THEN
329 l_rei_rec.rei_information28 := cur_rei_rec.rei_information28;
330 END IF;
331 IF l_rei_rec.rei_information29 IS NULL THEN
332 l_rei_rec.rei_information29 := cur_rei_rec.rei_information29;
333 END IF;
334 IF l_rei_rec.rei_information30 IS NULL THEN
335 l_rei_rec.rei_information30 := cur_rei_rec.rei_information30;
336 END IF;
337 END LOOP;
338 END LOOP;
339 p_rei_rec := l_rei_rec;
340 END IF;
341 EXCEPTION
342 WHEN OTHERS THEN
343 p_rei_rec := NULL;
344 raise;
345 END get_PAR_EI;
346 --
347 ---------------------------------------------------------------------------------------------
348 -- This one gets the details from the PAR Extra info for Performance appraisal
349 ---------------------------------------------------------------------------------------------
350 PROCEDURE get_per_sit_perf_appraisal(p_person_id IN NUMBER
351 ,p_effective_date IN DATE
352 ,p_rating_of_record_level OUT NOCOPY VARCHAR2
353 ,p_rating_of_record_pattern OUT NOCOPY VARCHAR2
354 ,p_rating_of_record_period OUT NOCOPY DATE
355 ,p_rating_of_rec_period_starts OUT NOCOPY DATE)
356 IS
357 --
358 l_special_info ghr_api.special_information_type;
359 l_emp_number per_people_f.employee_number%TYPE;
360 CURSOR c_per IS
361 SELECT per.employee_number
362 FROM per_all_people_f per -- Bug 4349372
363 WHERE per.person_id = p_person_id
364 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
365 AND per.effective_end_date;
366 BEGIN
367 ghr_api.return_special_information(p_person_id, 'US Fed Perf Appraisal',
368 p_effective_date, l_special_info);
369
370 IF l_special_info.object_version_number IS NOT NULL THEN
371 p_rating_of_record_level := l_special_info.segment5;
372 p_rating_of_record_pattern := l_special_info.segment4;
373 p_rating_of_record_period := fnd_date.canonical_to_date(l_special_info.segment6);
374 --Bug# 4753117 08-MAR-07 Veeramani adding Appraisal start date
375 p_rating_of_rec_period_starts := fnd_date.canonical_to_date(l_special_info.segment17);
376 --
377 -- added for NEW EHRI CHANGES Madhuri
378
379 ELSE -- Added select for bug# 1389262
380 DECLARE
381 l_effective_date DATE;
382 BEGIN
383 SELECT MAX(pan.date_from)
384 INTO l_effective_date
385 FROM per_person_analyses pan,
386 fnd_id_flex_structures flx
387 WHERE pan.id_flex_num = flx.id_flex_num
388 AND flx.id_flex_code = 'PEA'
389 AND flx.application_id = 800
390 AND flx.id_flex_structure_code = 'US_FED_PERF_APPRAISAL'
391 AND pan.person_id = p_person_id;
392 ghr_api.return_special_information(p_person_id, 'US Fed Perf Appraisal',
393 l_effective_date, l_special_info);
394 IF l_special_info.object_version_number IS NOT NULL THEN
395 p_rating_of_record_level := l_special_info.segment5;
396 p_rating_of_record_pattern := l_special_info.segment4;
397 p_rating_of_record_period := fnd_date.canonical_to_date(l_special_info.segment6);
398 --Bug# 4753117 07-MAR-07 Veeramani adding Appraisal start date
399 p_rating_of_rec_period_starts := fnd_date.canonical_to_date(l_special_info.segment17);
400
401 --KFF - Personal Analysis Flexfield, US_FED_PERF_APPRAISAL
402 --
403 -- added for NEW EHRI CHANGES MADHURI
404 /* Pradeep commented this for the Bug 4005811
405 ELSE
406 raise NO_DATA_FOUND;
407 End of Bug 4005811*/
408 END IF;
409 /*EXCEPTION
410 WHEN NO_DATA_FOUND THEN
411 -- Generate entry in PROCESS_LOG
412 OPEN c_per;
413 FETCH c_per INTO l_emp_number;
414 CLOSE c_per;
415 ghr_mto_int.log_message(p_procedure => 'No US Fed Perf Appraisal Info',
416 p_message => 'Employee number ' || l_emp_number ||
417 ' does not have US Fed Perf Appraisal ' ||
418 'on ' || TO_CHAR(p_effective_date, 'DD-MON-YYYY'));*/
419 END;
420 END IF;
421 EXCEPTION
422 WHEN OTHERS THEN
423 p_rating_of_record_level := NULL;
424 p_rating_of_record_pattern := NULL;
425 p_rating_of_record_period := NULL;
426 p_rating_of_rec_period_starts := NULL;
427 raise;
428 END get_per_sit_perf_appraisal;
429 --
430 ---------------------------------------------------------------------------------------------
431 -- This one gets the details from the PAR Extra info for NOAC specific
432 ---------------------------------------------------------------------------------------------
433 PROCEDURE get_PAR_EI_noac (p_pa_request_id IN NUMBER
434 ,p_first_noa_id IN NUMBER
435 ,p_second_noa_id IN NUMBER
436 ,p_noa_family_code IN VARCHAR2
437 ,p_person_id IN NUMBER
438 ,p_effective_date IN DATE
439 ,p_creditable_military_service OUT NOCOPY VARCHAR2
440 ,p_frozen_service OUT NOCOPY VARCHAR2
441 ,p_from_retirement_coverage OUT NOCOPY VARCHAR2
442 ,p_race_national_origin OUT NOCOPY VARCHAR2
443 ,p_handicap_code OUT NOCOPY VARCHAR2
444 ,p_ind_group_award OUT NOCOPY VARCHAR2
445 ,p_benefit_award OUT NOCOPY VARCHAR2
446 -- Added for new EHRI changes Madhuri 21-Jan-2005
447 ,p_leave_scd OUT NOCOPY DATE
448 ,p_scd_ses OUT NOCOPY DATE
449 ,p_scd_spcl_retire OUT NOCOPY DATE
450 ,p_ehri_employee_id OUT NOCOPY ghr_cpdf_temp.ehri_employee_id%TYPE --NUMBER
451 ,p_tsp_scd OUT NOCOPY DATE
452 ,p_scd_rif OUT NOCOPY DATE
453 ,p_scd_retirement OUT NOCOPY DATE
454 ,p_agency_use_code_field OUT NOCOPY VARCHAR2
455 ,p_agency_use_text_field OUT NOCOPY VARCHAR2
456 ,p_agency_data1 OUT NOCOPY VARCHAR2
457 ,p_agency_data2 OUT NOCOPY VARCHAR2
458 ,p_agency_data3 OUT NOCOPY VARCHAR2
459 ,p_agency_data4 OUT NOCOPY VARCHAR2
460 ,p_agency_data5 OUT NOCOPY VARCHAR2
461 ,p_race_ethnic_info OUT NOCOPY VARCHAR2
462 --Bug# 6158983
463 ,p_world_citizenship OUT NOCOPY VARCHAR2
464 -- 6312144 RPA-EIT Benefits
465 ,p_special_population_code OUT NOCOPY VARCHAR2
466 ,p_csrs_exc_appts OUT NOCOPY VARCHAR2
467 ,p_fers_exc_appts OUT NOCOPY VARCHAR2
468 ,p_fica_coverage_ind1 OUT NOCOPY VARCHAR2
469 ,p_fica_coverage_ind2 OUT NOCOPY VARCHAR2
470 ,p_fegli_assg_indicator OUT NOCOPY VARCHAR2
471 ,p_fegli_post_elc_basic_ins_amt OUT NOCOPY VARCHAR2
472 ,p_fegli_court_ord_ind OUT NOCOPY VARCHAR2
473 ,p_fegli_benf_desg_ind OUT NOCOPY VARCHAR2
474 ,p_fehb_event_code OUT NOCOPY VARCHAR2
475 ) IS
476 --
477 -- Bug#5168568 Removed Information type GHR_US_PAR_ETHNICITY_RACE
478 -- Having this EIT in the list is skipping the printing of Military service, frozen service.
479 CURSOR c_rit IS
480 SELECT rit.information_type
481 FROM ghr_noa_families nfa
482 ,ghr_pa_request_info_types rit
483 WHERE rit.noa_family_code = nfa.noa_family_code
484 AND (nfa.nature_of_action_id = p_first_noa_id
485 OR nfa.nature_of_action_id = p_second_noa_id)
486 AND rit.information_type IN ('GHR_US_PAR_AWARDS_BONUS' ,'GHR_US_PAR_APPT_INFO'
487 ,'GHR_US_PAR_APPT_TRANSFER' ,'GHR_US_PAR_CONV_APP'
488 ,'GHR_US_PAR_RETURN_TO_DUTY','GHR_US_PAR_CHG_RETIRE_PLAN'
489 ,'GHR_US_PAR_CHG_SCD');
490
491 CURSOR rpa_eit_ben
492 IS
493 SELECT rit.information_type
494 FROM ghr_noa_families nfa
495 ,ghr_pa_request_info_types rit
496 WHERE rit.noa_family_code = nfa.noa_family_code
497 AND (nfa.nature_of_action_id = p_first_noa_id
498 OR nfa.nature_of_action_id = p_second_noa_id)
499 AND rit.information_type IN ('GHR_US_PAR_BENEFIT_INFO' ,'GHR_US_PAR_RETIRMENT_SYS_INFO');
500 --
501 l_information_type ghr_pa_request_extra_info.information_type%TYPE;
502 l_rei ghr_pa_request_extra_info%ROWTYPE;
503 l_race_national_origin VARCHAR2(150);
504 l_handicap_code VARCHAR2(150);
505 l_per_ei_grp1_data per_people_extra_info%rowtype;
506
507 l_leave_scd DATE;
508 l_tsp_scd DATE;
509 l_ehri_employee_id ghr_cpdf_temp.ehri_employee_id%TYPE;
510 --- Data records retrieval bug 4284244
511 -- EHRI ID type problem
512 --
513
514 BEGIN
515 -- first get the information type for this NOA -- must only be one!!
516 FOR c_rit_rec IN c_rit LOOP
517 l_information_type := c_rit_rec.information_type;
518 END LOOP;
519
520 -- Only bother doing the rest if we got one we are interested in!
521 IF l_information_type IS NOT NULL THEN
522 get_PAR_EI (p_pa_request_id
523 ,p_noa_family_code
524 ,l_information_type
525 ,l_rei);
526 --
527
528 IF l_information_type = 'GHR_US_PAR_AWARDS_BONUS' THEN
529 p_ind_group_award := l_rei.rei_information6;
530 p_benefit_award := l_rei.rei_information7;
531 ELSIF l_information_type = 'GHR_US_PAR_APPT_INFO' THEN
532 p_creditable_military_service := l_rei.rei_information4;
533 p_frozen_service := l_rei.rei_information7;
534 p_from_retirement_coverage := l_rei.rei_information14;
535 l_race_national_origin := l_rei.rei_information16;
536 l_handicap_code := l_rei.rei_information8;
537 -- Disability Code
538 --
539 ELSIF l_information_type = 'GHR_US_PAR_APPT_TRANSFER' THEN
540 p_creditable_military_service := l_rei.rei_information6;
541 p_frozen_service := l_rei.rei_information9;
542 p_from_retirement_coverage := l_rei.rei_information16;
543 l_race_national_origin := l_rei.rei_information18;
544 l_handicap_code := l_rei.rei_information10;
545 -- Disability Code
546 --
547 ELSIF l_information_type = 'GHR_US_PAR_CONV_APP' THEN
548 p_creditable_military_service := l_rei.rei_information4;
549 p_frozen_service := l_rei.rei_information6;
550 p_from_retirement_coverage := l_rei.rei_information10;
551 l_race_national_origin := l_rei.rei_information12;
552 l_handicap_code := l_rei.rei_information7;
553 --
554 ELSIF l_information_type = 'GHR_US_PAR_RETURN_TO_DUTY' THEN
555 p_creditable_military_service := l_rei.rei_information3;
556 p_frozen_service := l_rei.rei_information5;
557 --
558 ELSIF l_information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN' THEN
559 p_creditable_military_service := l_rei.rei_information3;
560 p_frozen_service := l_rei.rei_information5;
561 p_from_retirement_coverage := l_rei.rei_information6;
562 --
563 ELSIF l_information_type = 'GHR_US_PAR_CHG_SCD' THEN
564 p_creditable_military_service := l_rei.rei_information5;
565 p_frozen_service := l_rei.rei_information6;
566 p_from_retirement_coverage := l_rei.rei_information7;
567 -- NEW EHRI DYNAMICS MADHURI
568 p_scd_ses := fnd_date.canonical_to_date(l_rei.rei_information10);
569 p_scd_spcl_retire := fnd_date.canonical_to_date(l_rei.rei_information11);
570 p_tsp_scd := fnd_date.canonical_to_date(l_rei.rei_information9);
571 p_scd_rif := fnd_date.canonical_to_date(l_rei.REI_INFORMATION3);
572 p_scd_retirement := fnd_date.canonical_to_date(l_rei.REI_INFORMATION8);
573 --
574 -- -- Bug 4724337 Race or National Origin changes
575 ELSIF l_information_type = 'GHR_US_PAR_ETHNICITY_RACE' THEN
576 IF l_rei.rei_information3 IS NOT NULL OR
577 l_rei.rei_information4 IS NOT NULL OR
578 l_rei.rei_information5 IS NOT NULL OR
579 l_rei.rei_information6 IS NOT NULL OR
580 l_rei.rei_information7 IS NOT NULL OR
581 l_rei.rei_information8 IS NOT NULL THEN
582 p_race_ethnic_info := NVL(l_rei.rei_information3,'0') || NVL(l_rei.rei_information4,'0') || NVL(l_rei.rei_information5,'0') ||
583 NVL(l_rei.rei_information6,'0') || NVL(l_rei.rei_information7,'0') || NVL(l_rei.rei_information8,'0');
584 END IF; -- IF l_rei.rei_informat
585 END IF;
586 END IF; -- IF l_information_type IS NOT
587 --
588 IF p_race_ethnic_info IS NULL THEN
589 -- Fetching Race and ethnicity category
590 l_per_ei_grp1_data := NULL;
591 ghr_history_fetch.fetch_peopleei
592 (p_person_id => p_person_id,
593 p_information_type => 'GHR_US_PER_ETHNICITY_RACE',
594 p_date_effective => p_effective_date,
595 p_per_ei_data => l_per_ei_grp1_data
596 );
597 -- Populate Race only if atleast one data segment is entered.
598 IF l_per_ei_grp1_data.pei_information3 IS NOT NULL OR
599 l_per_ei_grp1_data.pei_information4 IS NOT NULL OR
600 l_per_ei_grp1_data.pei_information5 IS NOT NULL OR
601 l_per_ei_grp1_data.pei_information6 IS NOT NULL OR
602 l_per_ei_grp1_data.pei_information7 IS NOT NULL OR
603 l_per_ei_grp1_data.pei_information8 IS NOT NULL THEN
604 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') ||
605 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');
606 END IF;
607 -- End Bug 4714292 EHRI Reports Changes for EOY 05
608 END IF; -- IF p_race_ethnic_info IS NULL THEN
609
610 --Incase the SCD's above are NULL then pick values from GHR_US_PER_SCD_INFORMATION
611 -- New EHRI changes Madhuri
612 ghr_history_fetch.fetch_peopleei
613 (p_person_id => p_person_id,
614 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
615 p_date_effective => nvl(p_effective_date,trunc(sysdate)),
616 p_per_ei_data => l_per_ei_grp1_data
617 );
618
619 l_leave_scd := fnd_date.canonical_to_date(l_per_ei_grp1_data.pei_information3);
620 p_leave_scd := l_leave_scd;
621 --
622 IF p_scd_rif IS NULL THEN
623 p_scd_rif := fnd_date.canonical_to_date(l_per_ei_grp1_data.pei_information5);
624 END IF;
625 --
626 IF p_tsp_scd IS NULL THEN
627 p_tsp_scd := fnd_date.canonical_to_date(l_per_ei_grp1_data.pei_information6);
628 END IF;
629 --
630 IF p_scd_retirement IS NULL THEN
631 p_scd_retirement := fnd_date.canonical_to_date(l_per_ei_grp1_data.pei_information7);
632 END IF;
633 --
634 IF p_scd_ses IS NULL THEN
635 p_scd_ses := fnd_date.canonical_to_date(l_per_ei_grp1_data.pei_information8);
636 END IF;
637 --
638 IF p_scd_spcl_retire IS NULL THEN
639 p_scd_spcl_retire := fnd_date.canonical_to_date(l_per_ei_grp1_data.pei_information9);
640 END IF;
641 --
642 -- bug 711711
643 -- if RNO or Handicap code was not filled then get them from HR Person EI
644
645 IF p_from_retirement_coverage IS NULL THEN
646 ghr_history_fetch.fetch_peopleei(
647 p_person_id => p_person_id,
648 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
649 p_date_effective => p_effective_date,
650 p_per_ei_data => l_per_ei_grp1_data);
651
652 p_from_retirement_coverage := l_per_ei_grp1_data.PEI_INFORMATION4;
653 END IF;
654 --
655 IF p_ehri_employee_id IS NULL THEN
656 ghr_history_fetch.fetch_peopleei(
657 p_person_id => p_person_id,
658 p_information_type => 'GHR_US_PER_GROUP1',
659 p_date_effective => p_effective_date,
660 p_per_ei_data => l_per_ei_grp1_data);
661
662 l_ehri_employee_id := l_per_ei_grp1_data.pei_information18;
663 END IF;
664 --
665
666 --Bug #6158983 code modified to person group1 information without checking any NULL
667 -- as same can be used to fetch world citizenship
668
669 -- IF l_race_national_origin IS NULL
670 -- OR l_handicap_code IS NULL THEN
671 --Bug#6158983
672
673 ghr_history_fetch.fetch_peopleei(
674 p_person_id => p_person_id,
675 p_information_type => 'GHR_US_PER_GROUP1',
676 p_date_effective => p_effective_date,
677 p_per_ei_data => l_per_ei_grp1_data);
678
679 IF l_race_national_origin IS NULL THEN
680 l_race_national_origin := l_per_ei_grp1_data.pei_information5;
681 -- Race and National Origin Code
682 END IF;
683 --
684 IF l_handicap_code IS NULL THEN
685 l_handicap_code := l_per_ei_grp1_data.pei_information11;
686 END IF;
687
688 --Bug#6158983
689 p_world_citizenship := l_per_ei_grp1_data.pei_information10;
690 --Endof Bug#6158983
691
692
693 --
694 --
695
696 -- END IF;
697
698 -- Fetch The agency related details under this
699 -- since US Fed Agency data isnt EIT of any particular NOAC and is associated with
700 -- call get_PAR_EI again for GHR_US_PAR_GEN_AGENCY_DATA
701
702 BEGIN
703 get_PAR_EI
704 (p_pa_request_id
705 ,p_noa_family_code
706 ,'GHR_US_PAR_GEN_AGENCY_DATA'
707 ,l_rei);
708
709 p_agency_use_code_field := l_rei.rei_information3;
710 p_agency_use_text_field := l_rei.rei_information3;
711 p_agency_data1 := l_rei.rei_information4;
712 p_agency_data2 := l_rei.rei_information5;
713 p_agency_data3 := l_rei.rei_information6;
714 p_agency_data4 := l_rei.rei_information7;
715 p_agency_data5 := l_rei.rei_information8;
716 END;
717
718 --6312144 RPA EIT Benefits
719 FOR c_rpa_eit_rec IN rpa_eit_ben
720 LOOP
721 l_rei := NULL;
722 l_information_type := c_rpa_eit_rec.information_type;
723 get_PAR_EI (p_pa_request_id
724 ,p_noa_family_code
725 ,l_information_type
726 ,l_rei);
727
728 IF l_information_type = 'GHR_US_PAR_RETIRMENT_SYS_INFO' THEN
729 p_special_population_code := l_rei.rei_information1;
730 p_csrs_exc_appts := l_rei.rei_information2;
731 p_fers_exc_appts := l_rei.rei_information3;
732 p_fica_coverage_ind1 := l_rei.rei_information4;
733 p_fica_coverage_ind2 := l_rei.rei_information5;
734
735 ELSIF l_information_type = 'GHR_US_PAR_BENEFIT_INFO' THEN
736 p_fegli_assg_indicator := l_rei.rei_information1;
737 p_fegli_post_elc_basic_ins_amt:= l_rei.rei_information2;
738 p_fegli_court_ord_ind := l_rei.rei_information3;
739 p_fegli_benf_desg_ind := l_rei.rei_information4;
740 p_fehb_event_code := l_rei.rei_information5;
741 END IF; -- IF l_information_type = 'GHR_US_P
742 END LOOP;
743 -- ENd of 6312144
744 p_race_national_origin := l_race_national_origin;
745 p_handicap_code := l_handicap_code;
746 p_ehri_employee_id := l_ehri_employee_id;
747 p_leave_scd := l_leave_scd;
748
749
750
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 p_creditable_military_service := NULL;
755 p_frozen_service := NULL;
756 p_from_retirement_coverage := NULL;
757 p_race_national_origin := NULL;
758 p_handicap_code := NULL;
759 p_ind_group_award := NULL;
760 p_benefit_award := NULL;
761 -- New eHRI changes Madhuri
762 p_leave_scd := NULL;
763 p_scd_ses := NULL;
764 p_scd_spcl_retire := NULL;
765 p_ehri_employee_id := NULL;
766 p_tsp_scd := NULL;
767 p_scd_rif := NULL;
768 p_scd_retirement := NULL;
769 p_agency_use_code_field := NULL;
770 p_agency_use_text_field := NULL;
771 p_agency_data1 := NULL;
772 p_agency_data2 := NULL;
773 p_agency_data3 := NULL;
774 p_agency_data4 := NULL;
775 p_agency_data5 := NULL;
776 p_world_citizenship := NULL;
777 p_special_population_code := NULL;
778 p_csrs_exc_appts := NULL;
779 p_fers_exc_appts := NULL;
780 p_fica_coverage_ind1 := NULL;
781 p_fica_coverage_ind2 := NULL;
782 p_fegli_assg_indicator := NULL;
783 p_fegli_post_elc_basic_ins_amt:= NULL;
784 p_fegli_court_ord_ind := NULL;
785 p_fegli_benf_desg_ind := NULL;
786 p_fehb_event_code := NULL;
787 raise;
788 END get_PAR_EI_noac;
789 --
790 --
791 PROCEDURE get_asg_details(p_pa_request_id IN NUMBER
792 ,p_person_id IN NUMBER
793 ,p_effective_date IN DATE
794 ,p_appt_nte_date OUT NOCOPY DATE)
795 IS
796 --
797 l_information_type ghr_pa_request_extra_info.information_type%TYPE;
798 l_rei ghr_pa_request_extra_info%ROWTYPE;
799 l_per_ei_grp1_data per_people_extra_info%rowtype;
800 l_appt_nte_date DATE;
801 --per_assignment_extra_info.aei_information4%TYPE;
802 --
803 CURSOR get_asg_id(p_request_id NUMBER,
804 p_person_id NUMBER)
805 IS
806 SELECT employee_assignment_id
807 FROM ghr_pa_requests
808 WHERE pa_request_id=p_request_id
809 AND person_id=p_person_id;
810 --
811 l_asg_id per_assignments_f.assignment_id%TYPE;
812 l_asg_ei_data per_assignment_extra_info%rowtype;
813
814 BEGIN
815 --
816 FOR get_asg_rec IN get_asg_id(p_pa_request_id, p_person_id)
817 LOOP
818 l_asg_id := get_asg_rec.employee_assignment_id;
819 END LOOP;
820
821 ghr_history_fetch.fetch_asgei
822 ( p_assignment_id => l_asg_id,
823 p_information_type => 'GHR_US_ASG_NTE_DATES',
824 p_date_effective => p_effective_date,
825 p_asg_ei_data => l_asg_ei_data
826 );
827
828 l_appt_nte_date := fnd_date.canonical_to_date(l_asg_ei_data.AEI_INFORMATION4);
829 --
830 p_appt_nte_date := l_appt_nte_date;
831
832 EXCEPTION
833 WHEN OTHERS THEN
834 p_appt_nte_date := NULL;
835 raise;
836 END get_asg_details;
837 --
838 --
839 ---------------------------------------------------------------------------------------------
840 -- This one gets the prior Work schedule and Pay Rate Determinant.
841 -- As an enhancement we should add these columns as well as prior duty station to the PAR
842 -- table since as is we have to go to history to get them!!
843 ---------------------------------------------------------------------------------------------
844 PROCEDURE get_prior_ws_prd_ds (p_pa_request_id IN NUMBER
845 ,p_altered_pa_request_id IN NUMBER
846 ,p_first_noa_id IN NUMBER
847 ,p_second_noa_id IN NUMBER
848 ,p_person_id IN NUMBER
849 ,p_employee_assignment_id IN NUMBER
850 ,p_from_position_id IN NUMBER
851 ,p_effective_date IN DATE
852 ,p_status IN VARCHAR2
853 ,p_from_work_schedule OUT NOCOPY VARCHAR2
854 ,p_from_pay_rate_determinant OUT NOCOPY VARCHAR2
855 ,p_from_duty_station_code OUT NOCOPY VARCHAR2) IS
856 --
857 l_pa_request_id NUMBER;
858 l_noa_id NUMBER;
859 --
860 l_asgei_data per_assignment_extra_info%ROWTYPE;
861 l_asgn_data per_all_assignments_f%ROWTYPE;
862 l_assignment_id NUMBER;
863 l_location_id NUMBER;
864 l_duty_station_id NUMBER;
865 l_duty_station_code ghr_duty_stations_f.duty_station_code%TYPE;
866 l_dummy_varchar VARCHAR2(2000);
867 l_dummy_number NUMBER;
868 BEGIN
869 -- If the PAR has happened then need to go to history to get it
870 -- otherwise we can use the same procedure the form, update hr and edits uses
871 IF p_status = 'UPDATE_HR_COMPLETE' THEN
872 --
873 IF p_altered_pa_request_id IS NULL THEN -- ie nothing is really being corrected
874 l_pa_request_id := p_pa_request_id;
875 l_noa_id := p_first_noa_id;
876 ELSE
877 l_pa_request_id := p_altered_pa_request_id;
878 l_noa_id := p_second_noa_id;
879 END IF;
880 --
881 -- Only need to even attempt to get these details is from position id is given!!
882 -- This means we wills tive give values for 5__ NOAC's even though the guide says
883 -- they are not needed?
884 --
885 IF p_from_position_id IS NOT NULL THEN
886 GHR_HISTORY_FETCH.fetch_asgei_prior_root_sf50(p_assignment_id => p_employee_assignment_id
887 ,p_information_type => 'GHR_US_ASG_SF52'
888 ,p_altered_pa_request_id => l_pa_request_id
889 ,p_noa_id_corrected => l_noa_id
890 ,p_date_effective => p_effective_date
891 ,p_asgei_data => l_asgei_data);
892 --
893 p_from_work_schedule := l_asgei_data.aei_information7;
894 p_from_pay_rate_determinant := l_asgei_data.aei_information6;
895 --
896 -- Now lets go get the location id which gives us the duty station
897 GHR_HISTORY_FETCH.fetch_asgn_prior_root_sf50(p_assignment_id => p_employee_assignment_id
898 ,p_altered_pa_request_id => l_pa_request_id
899 ,p_noa_id_corrected => l_noa_id
900 ,p_date_effective => p_effective_date
901 ,p_assignment_data => l_asgn_data);
902 --
903 ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_asgn_data.location_id
904 ,l_duty_station_id);
905 --
906 ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
907 ,p_effective_date
908 ,l_duty_station_code
909 ,l_dummy_varchar);
910 p_from_duty_station_code := l_duty_station_code;
911 END IF;
912 --
913 ELSE -- FUTURE_ACTION's
914 IF p_from_position_id IS NOT NULL THEN
915 l_assignment_id := p_employee_assignment_id;
916 GHR_API.sf52_from_data_elements
917 (p_person_id => p_person_id
918 ,p_assignment_id => l_assignment_id
919 ,p_effective_date => p_effective_date
920 ,p_altered_pa_request_id => null
921 ,p_noa_id_corrected => null
922 ,p_pa_history_id => null
923 ,p_position_id => l_dummy_number
924 ,p_position_title => l_dummy_varchar
925 ,p_position_number => l_dummy_varchar
926 ,p_position_seq_no => l_dummy_number
927 ,p_pay_plan => l_dummy_varchar
928 ,p_job_id => l_dummy_number
929 ,p_occ_code => l_dummy_varchar
930 ,p_grade_id => l_dummy_number
931 ,p_grade_or_level => l_dummy_varchar
932 ,p_step_or_rate => l_dummy_varchar
933 ,p_total_salary => l_dummy_number
934 ,p_pay_basis => l_dummy_varchar
935 -- FWFA Changes Bug#4444609
936 ,p_pay_table_identifier => l_dummy_number
937 -- FWFA Changes
938 ,p_basic_pay => l_dummy_number
939 ,p_locality_adj => l_dummy_number
940 ,p_adj_basic_pay => l_dummy_number
941 ,p_other_pay => l_dummy_number
942 ,p_au_overtime => l_dummy_number
943 ,p_auo_premium_pay_indicator => l_dummy_varchar
944 ,p_availability_pay => l_dummy_number
945 ,p_ap_premium_pay_indicator => l_dummy_varchar
946 ,p_retention_allowance => l_dummy_number
947 ,p_retention_allow_percentage=> l_dummy_number
948 ,p_supervisory_differential => l_dummy_number
949 ,p_supervisory_diff_percentage=> l_dummy_number
950 ,p_staffing_differential => l_dummy_number
951 ,p_staffing_diff_percentage => l_dummy_number
952 ,p_organization_id => l_dummy_number
953 ,p_position_org_line1 => l_dummy_varchar
954 ,p_position_org_line2 => l_dummy_varchar
955 ,p_position_org_line3 => l_dummy_varchar
956 ,p_position_org_line4 => l_dummy_varchar
957 ,p_position_org_line5 => l_dummy_varchar
958 ,p_position_org_line6 => l_dummy_varchar
959 ,p_duty_station_location_id => l_location_id
960 ,p_pay_rate_determinant => p_from_pay_rate_determinant
961 ,p_work_schedule => p_from_work_schedule);
962 --
963 ghr_pa_requests_pkg.get_SF52_loc_ddf_details (l_location_id
964 ,l_duty_station_id);
965
966 ghr_pa_requests_pkg.get_duty_station_details (l_duty_station_id
967 ,p_effective_date
968 ,l_duty_station_code
969 ,l_dummy_varchar);
970 p_from_duty_station_code := l_duty_station_code;
971 --
972 END IF;
973 END IF;
974 EXCEPTION
975 WHEN OTHERS THEN
976 p_from_work_schedule := NULL;
977 p_from_pay_rate_determinant := NULL;
978 p_from_duty_station_code := NULL;
979 raise;
980 END get_prior_ws_prd_ds;
981 --
982 ---------------------------------------------------------------------------------------------
983 -- For a 'Correction' record it gets the previous SSN if that is what is being corrected.
984 ---------------------------------------------------------------------------------------------
985 PROCEDURE get_prev_ssn (p_altered_pa_request_id IN NUMBER
986 ,p_employee_national_identifier IN VARCHAR2
987 ,p_noa_family_code IN VARCHAR2
988 ,p_from_national_identifier OUT NOCOPY VARCHAR2) IS
989 --
990 CURSOR cur_prev_ssn IS
991 SELECT par.employee_national_identifier prev_ssn
992 FROM ghr_pa_requests par
993 WHERE par.pa_request_id = p_altered_pa_request_id;
994 --
995 BEGIN
996 IF p_noa_family_code = 'CORRECT' THEN
997 FOR cur_prev_ssn_rec IN cur_prev_ssn LOOP
998 IF p_employee_national_identifier <> cur_prev_ssn_rec.prev_ssn THEN
999 p_from_national_identifier := format_ni(cur_prev_ssn_rec.prev_ssn);
1000 END IF;
1001 END LOOP;
1002 END IF;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 p_from_national_identifier := NULL;
1006 raise;
1007 END;
1008 --
1009
1010 FUNCTION get_equivalent_pay_plan(p_pay_plan IN ghr_pay_plans.pay_plan%TYPE)
1011 RETURN VARCHAR2 IS
1012 l_result ghr_pay_plans.equivalent_pay_plan%TYPE;
1013 BEGIN
1014 SELECT equivalent_pay_plan
1015 INTO l_result
1016 FROM ghr_pay_plans
1017 WHERE pay_plan = p_pay_plan;
1018 RETURN l_result;
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 l_result := NULL;
1022 RETURN l_result;
1023 END get_equivalent_pay_plan;
1024 -- Function get_loc_pay_area_code returns the LOCALITY PAY AREA CODE attached to the Duty station.
1025 -- Bug# 3231946 Added parameter p_duty_station_code to fix the bug.
1026 -- With the addition of new parameter, this function can be used to find
1027 -- the locality pay area code by passing either duty_station_id or duty_station_code.
1028 FUNCTION get_loc_pay_area_code(
1029 p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE default NULL,
1030 p_duty_station_code IN ghr_duty_stations_f.duty_station_code%TYPE default NULL,
1031 p_effective_date IN DATE)
1032 RETURN VARCHAR2 IS
1033 l_result ghr_locality_pay_areas_f.locality_pay_area_code%TYPE;
1034 BEGIN
1035
1036 IF p_duty_station_id is NOT NULL THEN
1037 SELECT lpa.locality_pay_area_code
1038 INTO l_result
1039 FROM ghr_locality_pay_areas_f lpa
1040 ,ghr_duty_stations_f dst
1041 WHERE dst.duty_station_id = p_duty_station_id
1042 AND NVL(p_effective_date,TRUNC(sysdate))
1043 BETWEEN dst.effective_start_date and dst.effective_end_date
1044 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
1045 AND NVL(p_effective_date,TRUNC(sysdate))
1046 BETWEEN lpa.effective_start_date and lpa.effective_end_date;
1047 ELSIF p_duty_station_code is NOT NULL THEN
1048 SELECT lpa.locality_pay_area_code
1049 INTO l_result
1050 FROM ghr_locality_pay_areas_f lpa
1051 ,ghr_duty_stations_f dst
1052 WHERE dst.duty_station_code = p_duty_station_code
1053 AND NVL(p_effective_date,TRUNC(sysdate))
1054 BETWEEN dst.effective_start_date and dst.effective_end_date
1055 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
1056 AND NVL(p_effective_date,TRUNC(sysdate))
1057 BETWEEN lpa.effective_start_date and lpa.effective_end_date;
1058 END IF;
1059
1060 RETURN l_result;
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 l_result := NULL;
1064 RETURN l_result;
1065 WHEN OTHERS THEN
1066 l_result := NULL;
1067 RETURN l_result;
1068 END get_loc_pay_area_code;
1069
1070 ---------------------------------------------------------------------------------------------
1071 -- C) This section includes any formating needed for certain fields.
1072 -- Note: some formating is done in the report, but if possible it should be done here!
1073 ---------------------------------------------------------------------------------------------
1074 --
1075 ---------------------------------------------------------------------------------------------
1076 -- This function takes the standard ni format ie 999-99-9999
1077 -- and returns it without the -'s
1078 ---------------------------------------------------------------------------------------------
1079 FUNCTION format_ni(p_ni IN VARCHAR2)
1080 RETURN VARCHAR2 IS
1081 BEGIN
1082 RETURN(REPLACE(p_ni,'-') );
1083 END format_ni;
1084 --
1085 ---------------------------------------------------------------------------------------------
1086 -- This function takes the standard possibly 4 char noa code and
1087 -- if it is 4 long returns the last three chars!
1088 ---------------------------------------------------------------------------------------------
1089 FUNCTION format_noac(p_noac IN VARCHAR2)
1090 RETURN VARCHAR2 IS
1091 BEGIN
1092 IF LENGTH (p_noac) = 4 THEN
1093 RETURN(SUBSTR(p_noac,2,3) );
1094 END IF;
1095 RETURN(p_noac);
1096 END format_noac;
1097 --
1098 ---------------------------------------------------------------------------------------------
1099 -- This function takes the duty station code and if the first 2 positions
1100 -- are chars replaces the last 3 chars with zeros!
1101 -- i.e. the duty station is 1) foreign OR 2)US Possesion or 3) US administered Area
1102 ---------------------------------------------------------------------------------------------
1103 FUNCTION format_ds(p_duty_station_code IN VARCHAR2)
1104 RETURN VARCHAR2 IS
1105 l_ds_2chars VARCHAR2(2);
1106 BEGIN
1107 l_ds_2chars := TRANSLATE(SUBSTR(upper(p_duty_station_code),1,2),
1108 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','**************************');
1109 IF l_ds_2chars = '**' THEN
1110 RETURN(SUBSTR(p_duty_station_code,1,6)||'000' );
1111 ELSE
1112 RETURN(p_duty_station_code);
1113 END IF;
1114 END format_ds;
1115 --
1116 ---------------------------------------------------------------------------------------------
1117 -- This function takes the employees first last and middle names and puts them into the
1118 -- format: last name comma first name space middle names - no longer than 23 chars
1119 ---------------------------------------------------------------------------------------------
1120 FUNCTION format_name (p_first_name IN VARCHAR2
1121 ,p_last_name IN VARCHAR2
1122 ,p_middle_name IN VARCHAR2)
1123 RETURN VARCHAR2 IS
1124 BEGIN
1125 RETURN(SUBSTR(p_last_name||','||p_first_name||' '||p_middle_name,1,23) );
1126 END format_name;
1127 ---------------------------------------------------------------------------------------------
1128 -- This function takes the any of the employees first, last or middle names and puts
1129 -- them back such that its not longer than 35 chars
1130 -- Added format_name_ehri for EHRI changes. The names cannot exceed more than 35 chars
1131 ---------------------------------------------------------------------------------------------
1132 FUNCTION format_name_ehri (p_name IN VARCHAR2)
1133 RETURN VARCHAR2 IS
1134 BEGIN
1135 RETURN(SUBSTR(p_name,1,35) );
1136 END format_name_ehri;
1137 ---------------------------------------------------------------------------------------------
1138 -- This function will format the basic pay depending on the pay basis
1139 ---------------------------------------------------------------------------------------------
1140 FUNCTION format_basic_pay(p_basic_pay IN NUMBER
1141 ,p_pay_basis IN VARCHAR2
1142 ,p_size IN NUMBER)
1143 RETURN VARCHAR2 IS
1144 BEGIN
1145 IF p_pay_basis IN ('PA','SY','PM','BW') THEN
1146 -- just report dollars
1147 IF p_basic_pay IS NOT NULL THEN
1148 RETURN ( LPAD(ROUND(p_basic_pay),p_size,'0') );
1149 ELSE
1150 RETURN(NULL);
1151 END IF;
1152 ELSIF p_pay_basis = 'WC' THEN
1153 -- report all zero's
1154 RETURN(SUBSTR('000000000000000000000',1,p_size) );
1155 ELSE
1156 -- report dollar and cents without decimal
1157 IF p_basic_pay IS NOT NULL THEN
1158 RETURN ( LPAD(ROUND(p_basic_pay,2) * 100,p_size,'0') );
1159 ELSE
1160 RETURN(NULL);
1161 END IF;
1162 END IF;
1163 --
1164 END format_basic_pay;
1165 --
1166 ---------------------------------------------------------------------------------------------
1167 -- This function is very simple just left pads the amount that was passed in with zero's to
1168 -- the size given
1169 ---------------------------------------------------------------------------------------------
1170 FUNCTION format_amount(p_amount IN NUMBER
1171 ,p_size IN NUMBER)
1172 RETURN VARCHAR2 IS
1173 BEGIN
1174 IF p_amount IS NOT NULL THEN
1175 RETURN ( LPAD(p_amount,p_size,'0') );
1176 ELSE
1177 RETURN(NULL);
1178 END IF;
1179 --
1180 END format_amount;
1181 --
1182 ---------------------------------------------------------------------------------------------
1183 -- This will insert one record into the GHR_CPDF_TEMP
1184 ---------------------------------------------------------------------------------------------
1185 --
1186 PROCEDURE insert_row (p_ghr_cpdf_temp_rec IN ghr_cpdf_temp%rowtype) IS
1187 BEGIN
1188
1189 INSERT INTO ghr_cpdf_temp(
1190 report_type,
1191 session_id,
1192 to_national_identifier,
1193 employee_date_of_birth,
1194 ehri_employee_id,
1195 agency_code,
1196 effective_date,
1197 first_noa_code,
1198 first_action_la_code1,
1199 first_action_la_code2,
1200 second_noa_code,
1201 NOA_CORRECTED, -- THIS IS NOT SUPPORTED, REPORT BLANK
1202 EFFECTIVE_DATE_CORRECTED, -- THIS IS NOT SUPPORTED, REPORT BLANK
1203 current_appointment_auth1,
1204 current_appointment_auth2,
1205 APPOINTMENT_NTE_DATE,
1206 personnel_office_id,
1207 organizational_component,
1208 sex,
1209 race_national_origin,
1210 handicap_code,
1211 SSN_CORRECTED,
1212 veterans_preference,
1213 tenure,
1214 AGENCY_USE_CODE_FIELD,
1215 AGENCY_USE_TEXT_FIELD,
1216 VETERANS_PREF_FOR_RIF,
1217 FEGLI,
1218 annuitant_indicator,
1219 retirement_plan,
1220 leave_SCD,
1221 SCD_retirement,
1222 SCD_rif,
1223 SCD_SES,
1224 SCD_SPCL_RETIRE,
1225 TSP_SCD,
1226 position_occupied,
1227 FLSA_category,
1228 appropriation_code, -- New
1229 bargaining_unit_status,
1230 supervisory_status,
1231 creditable_military_service,
1232 frozen_service,
1233 from_retirement_coverage,
1234 veterans_status,
1235 education_level,
1236 academic_discipline,
1237 year_degree_attained,
1238 rating_of_record_level,
1239 rating_of_record_pattern,
1240 RATING_OF_RECORD_PERIOD_STARTS, -- New
1241 rating_of_record_period_ends,
1242 PRIOR_FAMILY_NAME, --
1243 PRIOR_GIVEN_NAME, --
1244 PRIOR_MIDDLE_NAME, --
1245 PRIOR_NAME_SUFFIX, -- New
1246 PRIOR_POSITION_TITLE, --
1247 PRIOR_POSITION_NUMBER, --
1248 PRIOR_POSITION_ORG, --
1249 from_pay_plan,
1250 from_occ_code,
1251 from_grade_or_level,
1252 from_step_or_rate,
1253 from_pay_basis,
1254 from_TOTAL_SALARY,
1255 from_basic_pay,
1256 from_ADJ_BASIC_PAY,
1260 from_duty_station_code,
1257 from_locality_adj,
1258 from_work_schedule,
1259 from_pay_rate_determinant,
1261 employee_last_name,
1262 employee_first_name,
1263 employee_middle_names,
1264 name_title,
1265 position_title,
1266 POSITION_NUMBER,
1267 POSITION_ORG,
1268 to_pay_plan,
1269 to_occ_code,
1270 to_grade_or_level,
1271 to_step_or_rate,
1272 to_pay_basis,
1273 to_TOTAL_SALARY,
1274 to_basic_pay,
1275 to_ADJ_BASIC_PAY,
1276 to_locality_adj,
1277 to_supervisory_differential,
1278 to_retention_allowance,
1279 award_dollars,
1280 award_hours,
1281 award_percentage,
1282 to_work_schedule,
1283 PART_TIME_HOURS, --- can v have this as to_part_time_hours ?
1284 to_pay_rate_determinant,
1285 to_duty_station_code,
1286 AGENCY_DATA1,
1287 AGENCY_DATA2,
1288 AGENCY_DATA3,
1289 AGENCY_DATA4,
1290 AGENCY_DATA5,
1291 ACTION_APPROVAL_DATE,
1292 ACTION_AUTHR_FAMILY_NAME,
1293 ACTION_AUTHR_GIVEN_NAME,
1294 ACTION_AUTHR_MIDDLE_NAME,
1295 ACTION_AUTHR_NAME_SUFFIX,
1296 ACTION_AUTHR_TITLE,
1297 REMARKS_TEXT,
1298 race_ethnic_info,
1299 from_spl_rate_supplement,
1300 to_spl_rate_supplement,
1301 --Bug# 6158983
1302 world_citizenship,
1303 health_plan,
1304 special_population_code,
1305 csrs_exc_appts,
1306 fers_exc_appts,
1307 fica_coverage_ind1,
1308 fica_coverage_ind2,
1309 hyp_full_reg_duty_part_emp,
1310 fegli_assg_indicator,
1311 fegli_post_elc_basic_ins_amt,
1312 fegli_court_ord_ind,
1313 fegli_benf_desg_ind,
1314 fehb_event_code,
1315 pareq_last_updated_date,
1316 fehb_elect_eff_date
1317 --Bug# 6158983
1318 )
1319 VALUES(
1320 'DYNAMICS',
1321 USERENV('SESSIONID'),
1322 p_ghr_cpdf_temp_rec.to_national_identifier,
1323 p_ghr_cpdf_temp_rec.employee_date_of_birth,
1324 p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
1325 p_ghr_cpdf_temp_rec.agency_code,
1326 p_ghr_cpdf_temp_rec.effective_date,
1327 p_ghr_cpdf_temp_rec.first_noa_code,
1328 p_ghr_cpdf_temp_rec.first_action_la_code1,
1329 p_ghr_cpdf_temp_rec.first_action_la_code2,
1330 p_ghr_cpdf_temp_rec.second_noa_code,
1331 p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
1332 p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
1333 p_ghr_cpdf_temp_rec.current_appointment_auth1,
1334 p_ghr_cpdf_temp_rec.current_appointment_auth2,
1335 p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
1336 p_ghr_cpdf_temp_rec.personnel_office_id,
1337 p_ghr_cpdf_temp_rec.organizational_component,
1338 p_ghr_cpdf_temp_rec.sex,
1339 p_ghr_cpdf_temp_rec.race_national_origin,
1340 p_ghr_cpdf_temp_rec.handicap_code,
1341 p_ghr_cpdf_temp_rec.SSN_CORRECTED, --new
1342 p_ghr_cpdf_temp_rec.veterans_preference,
1343 p_ghr_cpdf_temp_rec.tenure,
1344 p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
1345 p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
1346 p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
1347 p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
1348 p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
1349 p_ghr_cpdf_temp_rec.retirement_plan,
1350 p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
1351 p_ghr_cpdf_temp_rec.SCD_retirement,
1352 p_ghr_cpdf_temp_rec.SCD_RIF,
1353 p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
1354 p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
1355 p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
1356 p_ghr_cpdf_temp_rec.position_occupied,
1357 p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
1358 p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
1359 p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
1360 p_ghr_cpdf_temp_rec.supervisory_status,
1361 p_ghr_cpdf_temp_rec.creditable_military_service,
1362 p_ghr_cpdf_temp_rec.frozen_service,
1363 p_ghr_cpdf_temp_rec.from_retirement_coverage,
1364 p_ghr_cpdf_temp_rec.veterans_status,
1365 p_ghr_cpdf_temp_rec.education_level,
1366 p_ghr_cpdf_temp_rec.academic_discipline,
1367 p_ghr_cpdf_temp_rec.year_degree_attained,
1368 p_ghr_cpdf_temp_rec.rating_of_record_level,
1369 p_ghr_cpdf_temp_rec.rating_of_record_pattern,
1370 p_ghr_cpdf_temp_rec.rating_of_record_period_starts, -- NEW
1371 p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
1372 p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
1373 p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
1374 p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
1375 p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
1376 p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
1377 p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
1378 p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
1379 p_ghr_cpdf_temp_rec.from_pay_plan,
1380 p_ghr_cpdf_temp_rec.from_occ_code,
1381 p_ghr_cpdf_temp_rec.from_grade_or_level,
1382 p_ghr_cpdf_temp_rec.from_step_or_rate,
1383 p_ghr_cpdf_temp_rec.from_pay_basis,
1384 p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
1385 p_ghr_cpdf_temp_rec.from_basic_pay,
1386 p_ghr_cpdf_temp_rec.from_adj_basic_pay,
1387 p_ghr_cpdf_temp_rec.from_locality_adj,
1388 p_ghr_cpdf_temp_rec.from_work_schedule,
1389 p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
1393 p_ghr_cpdf_temp_rec.employee_middle_names,
1390 p_ghr_cpdf_temp_rec.from_duty_station_code,
1391 p_ghr_cpdf_temp_rec.employee_last_name,
1392 p_ghr_cpdf_temp_rec.employee_first_name,
1394 p_ghr_cpdf_temp_rec.name_title,
1395 p_ghr_cpdf_temp_rec.position_title,
1396 p_ghr_cpdf_temp_rec.POSITION_NUMBER, -- NEW
1397 p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
1398 p_ghr_cpdf_temp_rec.to_pay_plan,
1399 p_ghr_cpdf_temp_rec.to_occ_code,
1400 p_ghr_cpdf_temp_rec.to_grade_or_level,
1401 p_ghr_cpdf_temp_rec.to_step_or_rate,
1402 p_ghr_cpdf_temp_rec.to_pay_basis,
1403 p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
1404 p_ghr_cpdf_temp_rec.to_basic_pay,
1405 p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
1406 p_ghr_cpdf_temp_rec.to_locality_adj,
1407 p_ghr_cpdf_temp_rec.to_supervisory_differential,
1408 p_ghr_cpdf_temp_rec.to_retention_allowance,
1409 p_ghr_cpdf_temp_rec.award_dollars,
1410 p_ghr_cpdf_temp_rec.award_hours,
1411 p_ghr_cpdf_temp_rec.award_percentage,
1412 p_ghr_cpdf_temp_rec.to_work_schedule,
1413 p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
1414 p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
1415 p_ghr_cpdf_temp_rec.to_duty_station_code,
1416 p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
1417 p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
1418 p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
1419 p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
1420 p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
1421 p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
1422 p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
1423 p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
1424 p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
1425 p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
1426 p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
1427 p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
1428 p_ghr_cpdf_temp_rec.race_ethnic_info,
1429 p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
1430 p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
1431 --Bug# 6158983
1432 p_ghr_cpdf_temp_rec.world_citizenship,
1433 p_ghr_cpdf_temp_rec.health_plan,
1434 p_ghr_cpdf_temp_rec.special_population_code,
1435 p_ghr_cpdf_temp_rec.csrs_exc_appts,
1436 p_ghr_cpdf_temp_rec.fers_exc_appts,
1437 p_ghr_cpdf_temp_rec.fica_coverage_ind1,
1438 p_ghr_cpdf_temp_rec.fica_coverage_ind2,
1439 p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
1440 p_ghr_cpdf_temp_rec.fegli_assg_indicator,
1441 p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
1442 p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
1443 p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
1444 p_ghr_cpdf_temp_rec.fehb_event_code,
1445 p_ghr_cpdf_temp_rec.pareq_last_updated_date,
1446 p_ghr_cpdf_temp_rec.fehb_elect_eff_date
1447 --Bug# 6158983
1448 );
1449
1450 COMMIT;
1451
1452 END insert_row;
1453 --
1454 ---------------------------------------------------------------------------
1455 --- THIS IS PROC TO GENERATE THE ASCII and XML file
1456 ---------------------------------------------------------------------------
1457 --
1458 PROCEDURE WritetoFile (p_input_file_name IN VARCHAR2,
1459 p_gen_xml_file IN VARCHAR2,
1460 p_gen_txt_file IN VARCHAR2
1461 )
1462 IS
1463 p_xml_fp UTL_FILE.FILE_TYPE;
1464 p_ascii_fp UTL_FILE.FILE_TYPE;
1465 l_audit_log_dir varchar2(500);
1466 l_xml_file_name varchar2(500);
1467 l_ascii_file_name varchar2(500);
1468 l_output_xml_fname varchar2(500);
1469 l_output_ascii_fname varchar2(500);
1470 v_tags t_tags;
1471 l_count NUMBER;
1472 l_session_id NUMBER;
1473 l_request_id NUMBER;
1474 l_temp VARCHAR2(500);
1475
1476
1477 --6158983
1478 prev_national_identifier GHR_CPDF_TEMP.to_national_identifier%type := null;
1479 prev_effective_date GHR_CPDF_TEMP.effective_date%type := null;
1480 prev_agency_code GHR_CPDF_TEMP.agency_code%type := null;
1481 eff_seq_no number;
1482 --6158983
1483
1484 CURSOR c_cpdf_dynamic(c_session_id NUMBER) IS
1485 SELECT *
1486 FROM GHR_CPDF_TEMP
1487 WHERE SESSION_ID = c_session_id
1488 AND report_type='DYNAMICS'
1489 ORDER BY agency_code,to_national_identifier,effective_date,pareq_last_updated_date;
1490 --
1491 CURSOR c_out_dir(c_request_id fnd_concurrent_requests.request_id%type) IS
1492 SELECT outfile_name
1493 FROM FND_CONCURRENT_REQUESTS
1494 WHERE request_id = c_request_id;
1495 --
1496 BEGIN
1497 -- Assigning the File name.
1498 l_xml_file_name := p_input_file_name || '.xml';
1499 l_ascii_file_name := p_input_file_name || '.txt';
1500 l_count := 1;
1501 l_session_id := USERENV('SESSIONID');
1502
1503 /* l_request_id := fnd_profile.VALUE('CONC_REQUEST_ID');
1504 FOR l_out_dir IN c_out_dir(l_request_id) LOOP
1505 l_temp := l_out_dir.outfile_name;
1506 END LOOP;
1507 l_audit_log_dir := SUBSTR(l_temp,1,INSTR(l_temp,'o'||l_request_id)-1); */
1508 --
1509 select value
1510 into l_audit_log_dir
1511 from v$parameter
1512 where name = 'utl_file_dir';
1513 -- Check whether more than one util file directory is found
1514 IF INSTR(l_audit_log_dir,',') > 0 THEN
1515 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
1516 END IF;
1517
1518 -- Find out whether the OS is MS or Unix/Linux based
1519 -- If it's greater than 0, it's Unix/Linux based environment
1520 IF INSTR(l_audit_log_dir,'/') > 0 THEN
1521 l_output_xml_fname := l_audit_log_dir || '/' || l_xml_file_name;
1522 l_output_ascii_fname := l_audit_log_dir || '/' || l_ascii_file_name;
1523 ELSE
1524 l_output_xml_fname := l_audit_log_dir || '\' || l_xml_file_name;
1525 l_output_ascii_fname := l_audit_log_dir || '\' || l_ascii_file_name;
1526 END IF;
1527
1528 -- fnd_file.put_line(fnd_file.log,'-----'||l_audit_log_dir);
1529 p_ascii_fp := utl_file.fopen(l_audit_log_dir,l_ascii_file_name,'w',32767);
1530
1531 IF p_gen_xml_file = 'Y' THEN
1532 p_xml_fp := utl_file.fopen(l_audit_log_dir,l_xml_file_name,'w',32767);
1533 utl_file.put_line(p_xml_fp,'<?xml version="1.0" encoding="UTF-8"?>');
1534 -- Writing from and to dates
1535 utl_file.put_line(p_xml_fp,'<Records>');
1536
1537 -- Loop through cursor and write the values into the XML and ASCII File.
1538
1539
1540 FOR ctr_table IN c_cpdf_dynamic(l_session_id) LOOP
1541
1542 if nvl(prev_national_identifier,'XXX') <> ctr_table.to_national_identifier or
1543 nvl(prev_agency_code,'X') <> ctr_table.agency_code or
1544 nvl(prev_effective_date,TO_DATE('01/01/1951','DD/MM/RRRR')) <> ctr_table.effective_date
1545 then
1546 g_eff_seq_no := 1;
1547 prev_national_identifier := ctr_table.to_national_identifier;
1548 prev_agency_code := ctr_table.agency_code;
1549 prev_effective_date := ctr_table.effective_date;
1550 else
1551 g_eff_seq_no := g_eff_seq_no + 1;
1552 end if;
1553
1554 WriteTagValues(ctr_table,v_tags);
1555 utl_file.put_line(p_xml_fp,'<Record' || l_count || '>');
1556 WriteXMLvalues(p_xml_fp,v_tags);
1557 utl_file.put_line(p_xml_fp,'</Record' || l_count || '>');
1558 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_txt_file);
1559 l_count := l_count + 1;
1560 END LOOP;
1561
1562 -- Write the end tag and close the XML File.
1563 utl_file.put_line(p_xml_fp,'</Records>');
1564 utl_file.fclose(p_xml_fp);
1565 ELSE
1566
1567 prev_national_identifier:= null;
1568 prev_agency_code := null;
1569 prev_effective_date := null;
1570
1571 -- Loop through cursor and write the values into the XML and ASCII File.
1572 FOR ctr_table IN c_cpdf_dynamic(l_session_id) LOOP
1573 if nvl(prev_national_identifier,'XXX') <> ctr_table.to_national_identifier or
1574 nvl(prev_agency_code,'XX') <> ctr_table.agency_code or
1575 nvl(prev_effective_date,TO_DATE('01/01/1951','DD/MM/RRRR')) <> ctr_table.effective_date THEN
1576 g_eff_seq_no := 1;
1577 prev_national_identifier := ctr_table.to_national_identifier;
1578 prev_agency_code := ctr_table.agency_code;
1579 prev_effective_date := ctr_table.effective_date;
1580 else
1581 g_eff_seq_no := nvl(g_eff_seq_no,0) + 1;
1582 end if;
1583 WriteTagValues(ctr_table,v_tags);
1584 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_txt_file);
1585 l_count := l_count + 1;
1586 END LOOP;
1587 END IF; -- IF p_gen_xml_file = 'Y' THEN
1588
1589 l_count := l_count - 1;
1590 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
1591 fnd_file.put_line(fnd_file.log,'Total Records : ' || l_count );
1592 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
1593
1594 IF p_gen_xml_file = 'Y' OR p_gen_txt_file = 'Y' THEN
1595 fnd_file.put_line(fnd_file.log,'------------Path of output file----------------');
1596 IF p_gen_xml_file = 'Y' THEN
1597 fnd_file.put_line(fnd_file.log,'XML file : ' || l_output_xml_fname);
1598 END IF;
1599 IF p_gen_txt_file = 'Y' THEN
1600 fnd_file.put_line(fnd_file.log,'Text file : ' || l_output_ascii_fname);
1601 END IF;
1602 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
1603 END IF;
1604
1605 END WritetoFile;
1606
1607 ---------------------------------------------------------------------------------------------
1608 -- This Procedure writes one record from the temporary table GHR_CPDF_TEMP
1609 -- to a PL/SQL table p_tags at a time. This PL/SQL table p_tags is used to write to file.
1610 ---------------------------------------------------------------------------------------------
1611
1612 PROCEDURE WriteTagValues(p_cpdf_dynamic GHR_CPDF_TEMP%rowtype,p_tags OUT NOCOPY t_tags)
1613 IS
1614 l_count NUMBER;
1615
1616
1617 BEGIN
1618 l_count := 1;
1619 -- Writing to Tags
1620 p_tags(l_count).tagname := 'Social_Security_Number';
1621 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.to_national_identifier,1,3) || '-' ||SUBSTR(p_cpdf_dynamic.to_national_identifier,4,2) || '-' ||SUBSTR(p_cpdf_dynamic.to_national_identifier,6) ;
1622 l_count := l_count+1;
1623
1624 p_tags(l_count).tagname := 'Birth_Date';
1625 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.employee_date_of_birth,'YYYY-MM-DD');
1626 l_count := l_count+1;
1627
1628 -- Check this
1629 p_tags(l_count).tagname := 'EHRI_Employee_ID';
1630 p_tags(l_count).tagvalue := p_cpdf_dynamic.EHRI_employee_id;
1631 l_count := l_count+1;
1632
1636
1633 p_tags(l_count).tagname := 'Agency_Subelement_Code';
1634 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_code;
1635 l_count := l_count+1;
1637 p_tags(l_count).tagname := 'Personnel_Action_Effective_Date';
1638 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.effective_date,'YYYY-MM-DD');
1639 l_count := l_count+1;
1640
1641 p_tags(l_count).tagname := 'Nature_of_Action_Code_1';
1642 p_tags(l_count).tagvalue := p_cpdf_dynamic.first_noa_code;
1643 l_count := l_count+1;
1644
1645 p_tags(l_count).tagname := 'Legal_Authority_Code_1';
1646 p_tags(l_count).tagvalue := p_cpdf_dynamic.first_action_la_code1;
1647 l_count := l_count+1;
1648
1649 p_tags(l_count).tagname := 'Legal_Authority_Code_2';
1650 p_tags(l_count).tagvalue := p_cpdf_dynamic.first_action_la_code2;
1651 l_count := l_count+1;
1652
1653 p_tags(l_count).tagname := 'Nature_of_Action_Code_2';
1654 p_tags(l_count).tagvalue := p_cpdf_dynamic.second_noa_code;
1655 l_count := l_count+1;
1656
1657 p_tags(l_count).tagname := 'Nature_of_Action_Being_Corrected';
1658 p_tags(l_count).tagvalue := NULL;
1659 l_count := l_count+1;
1660
1661 p_tags(l_count).tagname := 'Effective_Date_of_Personnel_Action_Being_Corrected';
1662 p_tags(l_count).tagvalue := NULL;
1663 l_count := l_count+1;
1664
1665 p_tags(l_count).tagname := 'Current_Appointment_Authority_Code_1';
1666 p_tags(l_count).tagvalue := p_cpdf_dynamic.current_appointment_auth1;
1667 l_count := l_count+1;
1668
1669 p_tags(l_count).tagname := 'Current_Appointment_Authority_Code_2';
1670 p_tags(l_count).tagvalue := p_cpdf_dynamic.current_appointment_auth2;
1671 l_count := l_count+1;
1672
1673 p_tags(l_count).tagname := 'Appointment_Not_to_Exceed_NTE_Date';
1674 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.appointment_nte_date,'YYYY-MM-DD');
1675 l_count := l_count+1;
1676
1677 p_tags(l_count).tagname := 'Personnel_Office_Identifier_Code';
1678 p_tags(l_count).tagvalue := p_cpdf_dynamic.personnel_office_id;
1679 l_count := l_count+1;
1680
1681 p_tags(l_count).tagname := 'Organizational_Component_Code';
1682 p_tags(l_count).tagvalue := p_cpdf_dynamic.organizational_component;
1683 l_count := l_count+1;
1684
1685 p_tags(l_count).tagname := 'Gender_Code';
1686 p_tags(l_count).tagvalue := p_cpdf_dynamic.sex;
1687 l_count := l_count+1;
1688
1689 p_tags(l_count).tagname := 'Race_and_National_Origin_Code';
1690 p_tags(l_count).tagvalue := p_cpdf_dynamic.race_national_origin;
1691 l_count := l_count+1;
1692
1693 p_tags(l_count).tagname := 'Disability_Code';
1694 p_tags(l_count).tagvalue := p_cpdf_dynamic.handicap_code;
1695 l_count := l_count+1;
1696
1697 -- Check
1698 p_tags(l_count).tagname := 'Social_Security_Number_Being_Corrected';
1699 IF p_cpdf_dynamic.ssn_corrected IS NOT NULL THEN
1700 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.ssn_corrected,1,3) || '-' ||SUBSTR(p_cpdf_dynamic.ssn_corrected,4,2) || '-' ||SUBSTR(p_cpdf_dynamic.ssn_corrected,6) ;
1701 ELSE
1702 p_tags(l_count).tagvalue := p_cpdf_dynamic.ssn_corrected;
1703 END IF;
1704 l_count := l_count+1;
1705
1706 p_tags(l_count).tagname := 'Veterans_Preference_Code';
1707 p_tags(l_count).tagvalue := p_cpdf_dynamic.veterans_preference;
1708 l_count := l_count+1;
1709
1710 p_tags(l_count).tagname := 'Tenure_Code';
1711 p_tags(l_count).tagvalue := p_cpdf_dynamic.tenure;
1712 l_count := l_count+1;
1713
1714 p_tags(l_count).tagname := 'Agency_Use_Code_Field';
1715 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_use_code_field;
1716 l_count := l_count+1;
1717
1718 p_tags(l_count).tagname := 'Agency_Use_Text_Field';
1719 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_use_text_field;
1720 l_count := l_count+1;
1721
1722 p_tags(l_count).tagname := 'Veterans_Preference_for_RIF_Indicator';
1723 p_tags(l_count).tagvalue := p_cpdf_dynamic.VETERANS_PREF_FOR_RIF;
1724 l_count := l_count+1;
1725
1726 p_tags(l_count).tagname := 'FEGLI_Code';
1727 p_tags(l_count).tagvalue := p_cpdf_dynamic.fegli;
1728 l_count := l_count+1;
1729
1730 p_tags(l_count).tagname := 'Annuitant_Code';
1731 p_tags(l_count).tagvalue := p_cpdf_dynamic.annuitant_indicator;
1732 l_count := l_count+1;
1733
1734 p_tags(l_count).tagname := 'Retirement_System_Type_Code';
1735 p_tags(l_count).tagvalue := p_cpdf_dynamic.retirement_plan;
1736 l_count := l_count+1;
1737
1738 p_tags(l_count).tagname := 'Leave_Service_Computation_Date';
1739 p_tags(l_count).tagvalue := to_char(p_cpdf_dynamic.leave_scd,'YYYY-MM-DD');
1740 l_count := l_count+1;
1741
1742 p_tags(l_count).tagname := 'Retirement_Service_Computation_Date';
1743 p_tags(l_count).tagvalue := to_char(p_cpdf_dynamic.scd_retirement,'YYYY-MM-DD');
1744 l_count := l_count+1;
1745
1746 p_tags(l_count).tagname := 'RIF_Service_Computation_Date';
1747 p_tags(l_count).tagvalue := to_char(p_cpdf_dynamic.scd_rif,'YYYY-MM-DD');
1748 l_count := l_count+1;
1749
1750 p_tags(l_count).tagname := 'SES_Service_Computation_Date';
1751 p_tags(l_count).tagvalue := to_char(p_cpdf_dynamic.scd_ses,'YYYY-MM-DD');
1752 l_count := l_count+1;
1753
1754 p_tags(l_count).tagname := 'Special_Retirement_Service_Computation_Date';
1755 p_tags(l_count).tagvalue := to_char(p_cpdf_dynamic.scd_spcl_retire,'YYYY-MM-DD');
1756 l_count := l_count+1;
1757
1758 p_tags(l_count).tagname := 'Thrift_Savings_Plan_Service_Computation_Date';
1759 p_tags(l_count).tagvalue := to_char(p_cpdf_dynamic.tsp_scd,'YYYY-MM-DD');
1763 p_tags(l_count).tagvalue := p_cpdf_dynamic.position_occupied;
1760 l_count := l_count+1;
1761
1762 p_tags(l_count).tagname := 'Position_Occupied_Code';
1764 l_count := l_count+1;
1765
1766 p_tags(l_count).tagname := 'FLSA_Category_Code';
1767 p_tags(l_count).tagvalue := p_cpdf_dynamic.flsa_category;
1768 l_count := l_count+1;
1769
1770 p_tags(l_count).tagname := 'Appropriation_Code';
1771 p_tags(l_count).tagvalue := p_cpdf_dynamic.appropriation_code;
1772 l_count := l_count+1;
1773
1774 p_tags(l_count).tagname := 'Bargaining_Unit_Code';
1775 p_tags(l_count).tagvalue := p_cpdf_dynamic.bargaining_unit_status;
1776 l_count := l_count+1;
1777
1778 p_tags(l_count).tagname := 'Supervisory_Type_Code';
1779 p_tags(l_count).tagvalue := p_cpdf_dynamic.supervisory_status;
1780 l_count := l_count+1;
1781
1782 p_tags(l_count).tagname := 'Creditable_Military_Service_Years';
1783 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.creditable_military_service,1,2);
1784 l_count := l_count+1;
1785
1786 p_tags(l_count).tagname := 'Creditable_Military_Service_Months';
1787 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.creditable_military_service,3,2);
1788 l_count := l_count+1;
1789
1790 p_tags(l_count).tagname := 'Creditable_Military_Service_Days';
1791 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.creditable_military_service,5);
1792 l_count := l_count+1;
1793
1794 p_tags(l_count).tagname := 'Frozen_Service_Years';
1795 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.frozen_service,1,2);
1796 l_count := l_count+1;
1797
1798 p_tags(l_count).tagname := 'Frozen_Service_Months';
1799 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.frozen_service,3,2);
1800 l_count := l_count+1;
1801
1802 p_tags(l_count).tagname := 'Frozen_Service_Days';
1803 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_dynamic.frozen_service,5);
1804 l_count := l_count+1;
1805
1806 p_tags(l_count).tagname := 'Retirement_Previous_Coverage_Indicator';
1807 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_retirement_coverage;
1808
1809 /*IF p_cpdf_dynamic.first_noa_code <> '001' THEN
1810 IF p_cpdf_dynamic.from_retirement_coverage IS NOT NULL THEN
1811 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_retirement_coverage;
1812 ELSE
1813 p_tags(l_count).tagvalue := NVL(p_cpdf_dynamic.from_retirement_coverage,'NA');
1814 END IF;
1815 ELSE
1816 p_tags(l_count).tagvalue := NULL;
1817 END If;*/
1818 l_count := l_count+1;
1819 -- changed the column name from retirement_prev_covr to from_retirement_coverage
1820
1821 p_tags(l_count).tagname := 'Veterans_Status_Code';
1822 p_tags(l_count).tagvalue := p_cpdf_dynamic.veterans_status;
1823 l_count := l_count+1;
1824
1825 p_tags(l_count).tagname := 'Education_Level_Code';
1826 p_tags(l_count).tagvalue := p_cpdf_dynamic.education_level;
1827 l_count := l_count+1;
1828
1829 p_tags(l_count).tagname := 'Instructional_Program_Code';
1830 p_tags(l_count).tagvalue := p_cpdf_dynamic.academic_discipline;
1831 l_count := l_count+1;
1832
1833 p_tags(l_count).tagname := 'Degree_Year';
1834 p_tags(l_count).tagvalue := p_cpdf_dynamic.year_degree_attained;
1835 l_count := l_count+1;
1836
1837 p_tags(l_count).tagname := 'Rating_of_Record_Level_Code';
1838 p_tags(l_count).tagvalue := p_cpdf_dynamic.rating_of_record_level;
1839 l_count := l_count+1;
1840
1841 p_tags(l_count).tagname := 'Rating_of_Record_Pattern_Code';
1842 p_tags(l_count).tagvalue := p_cpdf_dynamic.rating_of_record_pattern;
1843 l_count := l_count+1;
1844
1845 p_tags(l_count).tagname := 'Rating_of_Record_Period_Start_Date';
1846 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.rating_of_record_period_starts,'YYYY-MM-DD');
1847 l_count := l_count+1;
1848
1849 p_tags(l_count).tagname := 'Rating_of_Record_Period_End_Date';
1850 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.rating_of_record_period_ends,'YYYY-MM-DD');
1851 l_count := l_count+1;
1852
1853 p_tags(l_count).tagname := 'Prior_Family_Name';
1854 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_family_name;
1855 l_count := l_count+1;
1856
1857 p_tags(l_count).tagname := 'Prior_Given_Name';
1858 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_given_name;
1859 l_count := l_count+1;
1860
1861 p_tags(l_count).tagname := 'Prior_Middle_Name';
1862 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_middle_name;
1863 l_count := l_count+1;
1864
1865 p_tags(l_count).tagname := 'Prior_Name_Suffix';
1866 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_name_suffix;
1867 l_count := l_count+1;
1868
1869 p_tags(l_count).tagname := 'Prior_Position_Title';
1870 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_position_title;
1871 l_count := l_count+1;
1872
1873 p_tags(l_count).tagname := 'Prior_Position_Number';
1874 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_position_number;
1875 l_count := l_count+1;
1876
1877 p_tags(l_count).tagname := 'Prior_Position_Organization';
1878 p_tags(l_count).tagvalue := p_cpdf_dynamic.prior_position_org;
1879 l_count := l_count+1;
1880
1881 p_tags(l_count).tagname := 'Prior_Pay_Plan_Code';
1882 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_pay_plan;
1883 l_count := l_count+1;
1884
1885 p_tags(l_count).tagname := 'Prior_Occupational_Series_Type_Code';
1886 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_occ_code;
1887 l_count := l_count+1;
1888
1889 p_tags(l_count).tagname := 'Prior_Grade_Level_Code';
1893 p_tags(l_count).tagname := 'Prior_Step_or_Rate_Type_Code';
1890 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_grade_or_level;
1891 l_count := l_count+1;
1892
1894 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_step_or_rate;
1895 l_count := l_count+1;
1896
1897 p_tags(l_count).tagname := 'Prior_Pay_Basis_Type_Code';
1898 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_pay_basis;
1899 l_count := l_count+1;
1900
1901 -- Begin Bug# 5562815
1902 IF p_cpdf_dynamic.from_pay_basis <> 'PA' THEN
1903
1904 p_tags(l_count).tagname := 'Prior_Total_Pay_Rate';
1905 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_dynamic.from_total_salary,'99999999.99'));
1906 l_count := l_count+1;
1907
1908 p_tags(l_count).tagname := 'Prior_Basic_Pay';
1909 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_dynamic.from_basic_pay,'99999999.99'));
1910 l_count := l_count+1;
1911
1912 p_tags(l_count).tagname := 'Prior_Adjusted_Basic_Pay_Amount';
1913 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_dynamic.from_adj_basic_pay,'99999999.99'));
1914 l_count := l_count+1;
1915 ELSE
1916 p_tags(l_count).tagname := 'Prior_Total_Pay_Rate';
1917 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_total_salary;
1918 l_count := l_count+1;
1919
1920 p_tags(l_count).tagname := 'Prior_Basic_Pay';
1921 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_basic_pay;
1922 l_count := l_count+1;
1923
1924 p_tags(l_count).tagname := 'Prior_Adjusted_Basic_Pay_Amount';
1925 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_adj_basic_pay;
1926 l_count := l_count+1;
1927 END IF;
1928 -- End Bug# 5562815
1929
1930 p_tags(l_count).tagname := 'Prior_Locality_Pay_Amount';
1931 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_locality_adj;
1932 l_count := l_count+1;
1933
1934 p_tags(l_count).tagname := 'Prior_Work_Schedule_Code';
1935 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_work_schedule;
1936 l_count := l_count+1;
1937
1938 p_tags(l_count).tagname := 'Prior_Pay_Rate_Determinant_Type_Code';
1939 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_pay_rate_determinant;
1940 l_count := l_count+1;
1941
1942 p_tags(l_count).tagname := 'Prior_Duty_Station_Code';
1943 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_duty_station_code;
1944 l_count := l_count+1;
1945
1946 p_tags(l_count).tagname := 'Family_Name';
1947 p_tags(l_count).tagvalue := p_cpdf_dynamic.employee_last_name;
1948 l_count := l_count+1;
1949
1950 p_tags(l_count).tagname := 'Given_Name';
1951 p_tags(l_count).tagvalue := p_cpdf_dynamic.employee_first_name;
1952 l_count := l_count+1;
1953
1954 p_tags(l_count).tagname := 'Middle_Name';
1955 p_tags(l_count).tagvalue := p_cpdf_dynamic.employee_middle_names;
1956 l_count := l_count+1;
1957
1958 p_tags(l_count).tagname := 'Name_Suffix';
1959 p_tags(l_count).tagvalue := p_cpdf_dynamic.name_title;
1960 l_count := l_count+1;
1961
1962 p_tags(l_count).tagname := 'Position_Title';
1963 p_tags(l_count).tagvalue := p_cpdf_dynamic.position_title;
1964 l_count := l_count+1;
1965
1966 p_tags(l_count).tagname := 'Position_Number';
1967 p_tags(l_count).tagvalue := p_cpdf_dynamic.position_number;
1968 l_count := l_count+1;
1969
1970 p_tags(l_count).tagname := 'Position_Organization';
1971 p_tags(l_count).tagvalue := p_cpdf_dynamic.position_org;
1972 l_count := l_count+1;
1973
1974 p_tags(l_count).tagname := 'Pay_Plan_Code';
1975 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_pay_plan;
1976 l_count := l_count+1;
1977
1978 p_tags(l_count).tagname := 'Occupational_Series_Type_Code';
1979 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_occ_code;
1980 l_count := l_count+1;
1981
1982 p_tags(l_count).tagname := 'Grade_Level_Code';
1983 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_grade_or_level;
1984 l_count := l_count+1;
1985
1986 p_tags(l_count).tagname := 'Step_or_Rate_Type_Code';
1987 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_step_or_rate;
1988 l_count := l_count+1;
1989
1990 p_tags(l_count).tagname := 'Pay_Basis_Type_Code';
1991 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_pay_basis;
1992 l_count := l_count+1;
1993
1994 -- Begin Bug# 5562815
1995 IF p_cpdf_dynamic.to_pay_basis <> 'PA' THEN
1996 p_tags(l_count).tagname := 'Total_Pay_Rate';
1997 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_dynamic.to_total_salary,'99999999.99'));
1998 l_count := l_count+1;
1999
2000 p_tags(l_count).tagname := 'Basic_Pay_Amount';
2001 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_dynamic.to_basic_pay,'99999999.99'));
2002 l_count := l_count+1;
2003
2004 p_tags(l_count).tagname := 'Adjusted_Basic_Pay_Amount';
2005 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_dynamic.to_adj_basic_pay,'99999999.99'));
2006 l_count := l_count+1;
2007 ELSE
2008 p_tags(l_count).tagname := 'Total_Pay_Rate';
2009 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_total_salary;
2010 l_count := l_count+1;
2011
2012 p_tags(l_count).tagname := 'Basic_Pay_Amount';
2013 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_basic_pay;
2014 l_count := l_count+1;
2015
2016 p_tags(l_count).tagname := 'Adjusted_Basic_Pay_Amount';
2017 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_adj_basic_pay;
2018 l_count := l_count+1;
2019 END IF;
2020 -- End Bug# 5562815
2021
2022 p_tags(l_count).tagname := 'Locality_Pay_Amount';
2023 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_locality_adj;
2024 l_count := l_count+1;
2025
2026 p_tags(l_count).tagname := 'Supervisor_Differential_Amount';
2027 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_supervisory_differential;
2028 l_count := l_count+1;
2029
2030 p_tags(l_count).tagname := 'Retention_Allowance_Amount';
2031 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_retention_allowance;
2032 l_count := l_count+1;
2033
2034 p_tags(l_count).tagname := 'Award_Dollars';
2035 p_tags(l_count).tagvalue := p_cpdf_dynamic.award_dollars;
2036 l_count := l_count+1;
2037
2038 p_tags(l_count).tagname := 'Award_Hours';
2039 p_tags(l_count).tagvalue := p_cpdf_dynamic.award_hours;
2040 l_count := l_count+1;
2041
2042 p_tags(l_count).tagname := 'Award_Percent';
2043 p_tags(l_count).tagvalue := p_cpdf_dynamic.award_percentage;
2044 l_count := l_count+1;
2045
2046 p_tags(l_count).tagname := 'Work_Schedule_Code';
2047 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_work_schedule;
2048 l_count := l_count+1;
2049
2050 p_tags(l_count).tagname := 'Part_Time_Hours';
2051 p_tags(l_count).tagvalue := p_cpdf_dynamic.part_time_hours;
2052 l_count := l_count+1;
2053
2054 p_tags(l_count).tagname := 'Pay_Rate_Determinant_Type_Code';
2055 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_pay_rate_determinant;
2056 l_count := l_count+1;
2057
2058 p_tags(l_count).tagname := 'Duty_Station_Code';
2059 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_duty_station_code;
2060 l_count := l_count+1;
2061
2062 p_tags(l_count).tagname := 'Agency_Data_1';
2063 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_data1;
2064 l_count := l_count+1;
2065
2066 p_tags(l_count).tagname := 'Agency_Data_2';
2067 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_data2;
2068 l_count := l_count+1;
2069
2070 p_tags(l_count).tagname := 'Agency_Data_3';
2071 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_data3;
2072 l_count := l_count+1;
2073
2074 p_tags(l_count).tagname := 'Agency_Data_4';
2075 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_data4;
2076 l_count := l_count+1;
2077
2078 p_tags(l_count).tagname := 'Agency_Data_5';
2079 p_tags(l_count).tagvalue := p_cpdf_dynamic.agency_data5;
2080 l_count := l_count+1;
2081
2082 p_tags(l_count).tagname := 'Personnel_Action_Approval_Date';
2083 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.action_approval_date,'YYYY-MM-DD');
2084 l_count := l_count+1;
2085
2086 p_tags(l_count).tagname := 'Personnel_Action_Authorizer_Family_Name';
2087 p_tags(l_count).tagvalue := p_cpdf_dynamic.action_authr_family_name;
2088 l_count := l_count+1;
2089
2090 p_tags(l_count).tagname := 'Personnel_Action_Authorizer_Given_Name';
2091 p_tags(l_count).tagvalue := p_cpdf_dynamic.action_authr_given_name;
2092 l_count := l_count+1;
2093
2094 p_tags(l_count).tagname := 'Personnel_Action_Authorizer_Middle_Name';
2095 p_tags(l_count).tagvalue := p_cpdf_dynamic.action_authr_middle_name;
2096 l_count := l_count+1;
2097
2098 p_tags(l_count).tagname := 'Personnel_Action_Authorizer_Name_Suffix';
2099 p_tags(l_count).tagvalue := p_cpdf_dynamic.action_authr_name_suffix;
2100 l_count := l_count+1;
2101
2102 p_tags(l_count).tagname := 'Personnel_Action_Authorizer_Title';
2103 p_tags(l_count).tagvalue := p_cpdf_dynamic.action_authr_title;
2104 l_count := l_count+1;
2105
2106 p_tags(l_count).tagname := 'Remarks_Text';
2107 p_tags(l_count).tagvalue := p_cpdf_dynamic.remarks_text;
2108 l_count := l_count+1;
2109
2110 -- Bug 4714292 EHRI Reports Changes for EOY 05
2111 p_tags(l_count).tagname := 'Prior_Special_Rate_Supplement';
2112 p_tags(l_count).tagvalue := p_cpdf_dynamic.from_spl_rate_supplement;
2113 l_count := l_count+1;
2114
2115 p_tags(l_count).tagname := 'Special_Rate_Supplement';
2116 p_tags(l_count).tagvalue := p_cpdf_dynamic.to_spl_rate_supplement;
2117 l_count := l_count+1;
2118
2119 p_tags(l_count).tagname := 'Ethnicity_Code';
2120 p_tags(l_count).tagvalue := p_cpdf_dynamic.race_ethnic_info;
2121 l_count := l_count+1;
2122 -- End Bug 4714292 EHRI Reports Changes for EOY 05
2123
2124
2125 -- Bug 6158983
2126 p_tags(l_count).tagname := 'Citizenship_Country_Code';
2127 p_tags(l_count).tagvalue := p_cpdf_dynamic.world_citizenship;
2128 l_count := l_count+1;
2129
2130 p_tags(l_count).tagname := 'Special_Population_Code';
2131 p_tags(l_count).tagvalue := p_cpdf_dynamic.special_population_code;
2132 l_count := l_count+1;
2133
2134 p_tags(l_count).tagname := 'Appointment_Excluded_From_CSRS_Indicator';
2135 p_tags(l_count).tagvalue := p_cpdf_dynamic.csrs_exc_appts;
2136 l_count := l_count+1;
2137
2138 p_tags(l_count).tagname := 'Appointment_Excluded_From_FERS_Indicator';
2139 p_tags(l_count).tagvalue := p_cpdf_dynamic.fers_exc_appts;
2140 l_count := l_count+1;
2141
2142 p_tags(l_count).tagname := 'FICA_Coverage_Indicator_1';
2143 p_tags(l_count).tagvalue := p_cpdf_dynamic.fica_coverage_ind1;
2144 l_count := l_count+1;
2145
2146 p_tags(l_count).tagname := 'FICA_Coverage_Indicator_2';
2147 p_tags(l_count).tagvalue := p_cpdf_dynamic.fica_coverage_ind2;
2148 l_count := l_count+1;
2149
2150
2151
2152
2153 p_tags(l_count).tagname := 'Person_Action_Effective_Sequence';
2154 p_tags(l_count).tagvalue := nvl(g_eff_seq_no,1);
2155 l_count := l_count+1;
2156
2157
2158
2159 p_tags(l_count).tagname := 'Hypothetical_Fulltime_Reg_Tour_Duty_Part_time_Employees';
2160 p_tags(l_count).tagvalue := p_cpdf_dynamic.hyp_full_reg_duty_part_emp;
2161 l_count := l_count+1;
2162
2163 p_tags(l_count).tagname := 'FEGLI_Assignment_Indicator';
2164 p_tags(l_count).tagvalue := p_cpdf_dynamic.fegli_assg_indicator;
2165 l_count := l_count+1;
2166
2167 p_tags(l_count).tagname := 'FEGLI_Post_Election_Basic_Insurance_Amount';
2168 p_tags(l_count).tagvalue := p_cpdf_dynamic.fegli_post_elc_basic_ins_amt;
2169 l_count := l_count+1;
2170
2171 p_tags(l_count).tagname := 'Court_Orders_for_FEGLI_Purposes_Indicator';
2172 p_tags(l_count).tagvalue := p_cpdf_dynamic.fegli_court_ord_ind;
2173 l_count := l_count+1;
2174
2175 p_tags(l_count).tagname := 'Designation_FEGLI_Beneficiaries_Indicator';
2176 p_tags(l_count).tagvalue := p_cpdf_dynamic.fegli_benf_desg_ind;
2177 l_count := l_count+1;
2178
2179 p_tags(l_count).tagname := 'Federal_Employees_Health_Benefits_FEHB_Plan_Code';
2180 p_tags(l_count).tagvalue := p_cpdf_dynamic.health_plan;
2181 l_count := l_count+1;
2182
2183 p_tags(l_count).tagname := 'Federal_Employees_Health_Benefits_FEHB_Event_Code';
2184 p_tags(l_count).tagvalue := p_cpdf_dynamic.fehb_event_code;
2185 l_count := l_count+1;
2186
2187 p_tags(l_count).tagname := 'Federal_Employees_Health_Benefits_Effective_Date';
2188 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_dynamic.fehb_elect_eff_date,'YYYY-MM-DD');
2189 l_count := l_count+1;
2190
2191
2192 --End Bug 6158983
2193
2194
2195 END WriteTagValues;
2196
2197 -----------------------------------------------------------------------------
2198 -- Writing the records from PL/SQL table p_tags into XML File
2199 -----------------------------------------------------------------------------
2200 PROCEDURE WriteXMLvalues(p_l_fp utl_file.file_type, p_tags t_tags )
2201 IS
2202 BEGIN
2203 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
2204 utl_file.put_line(p_l_fp,'<' || p_tags(l_tags).tagname || '>' || p_tags(l_tags).tagvalue || '</' || p_tags(l_tags).tagname || '>');
2205 END LOOP;
2206 END WriteXMLvalues;
2207
2208 -----------------------------------------------------------------------------
2209 -- Writing the records from PL/SQL table p_tags into Text and FND Output File
2210 -----------------------------------------------------------------------------
2211 PROCEDURE WriteAsciivalues(p_l_fp utl_file.file_type,
2212 p_tags t_tags,
2213 p_gen_txt_file IN VARCHAR2)
2214 IS
2215 l_temp VARCHAR2(4000);
2216 l_tot NUMBER;
2217 BEGIN
2218 l_tot := p_tags.COUNT;
2219 IF l_tot > 0 THEN
2220 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
2221 IF l_tags = l_tot THEN
2222 l_temp := p_tags(l_tags).tagvalue;
2223 IF p_gen_txt_file = 'Y' THEN
2224 utl_file.put_line(p_l_fp,l_temp);
2225 END IF;
2226 fnd_file.put_line(fnd_file.output,l_temp);
2227 ELSE
2228 l_temp := p_tags(l_tags).tagvalue || '|';
2229 IF p_gen_txt_file = 'Y' THEN
2230 utl_file.put(p_l_fp,l_temp);
2231 END IF;
2232 fnd_file.put(fnd_file.output,l_temp);
2233 END IF;
2234 END LOOP;
2235 END IF;
2236
2237 END WriteAsciivalues;
2238
2239 PROCEDURE get_suffix_lname(p_last_name in varchar2,
2240 p_report_date in date,
2241 p_suffix out nocopy varchar2,
2242 p_lname out nocopy varchar2)
2243 IS
2244 l_suffix_pos number;
2245 l_total_len number;
2246 l_proc varchar2(30) := 'get_suffix_lname';
2247
2248 CURSOR GET_SUFFIX IS
2249 SELECT INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
2250 LENGTH(p_last_name)
2251 FROM HR_LOOKUPS
2252 WHERE LOOKUP_TYPE = 'GHR_US_NAME_SUFFIX'
2253 AND TRUNC(p_report_date) BETWEEN NVL(START_DATE_ACTIVE ,p_report_date)
2254 AND NVL(END_DATE_ACTIVE,p_report_date)
2255 AND RTRIM(SUBSTR(TRANSLATE(UPPER(p_last_name),',.',' '),
2256 INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
2257 LENGTH(p_last_name)),' ') = ' '||UPPER(LOOKUP_CODE)
2258 AND ROWNUM = 1;
2259 BEGIN
2260
2261 hr_utility.set_location('Entering:'||l_proc,5);
2262
2263 IF GET_SUFFIX%ISOPEN THEN
2264 CLOSE GET_SUFFIX;
2265 END IF;
2266
2267 OPEN GET_SUFFIX;
2268 --getting the position of a suffix appended in the lastname by comparing the lastname
2269 -- with the suffixes available in lookup*/
2270 FETCH GET_SUFFIX INTO l_suffix_pos, l_total_len;
2271 IF GET_SUFFIX%NOTFOUND THEN
2272 p_lname := RTRIM(p_last_name,' ,.');
2273 p_suffix := NULL;
2274 ELSE
2275 p_lname := RTRIM(SUBSTR(p_last_name, 0, l_suffix_pos-1),' ,.');
2276 p_suffix := SUBSTR(p_last_name,l_suffix_pos+1,l_total_len);
2277 END IF;
2278 CLOSE GET_SUFFIX;
2279
2280 END get_suffix_lname;
2281
2282
2283 ---------------------------------------------------------------------------------------------
2284 -- This is the procedure to populate values into the temporary table GHR_CPDF_TEMP
2285 ---------------------------------------------------------------------------------------------
2286 PROCEDURE populate_ghr_cpdf_temp(p_agency IN VARCHAR2
2287 ,p_start_date IN DATE
2288 ,p_end_date IN DATE
2289 ,p_count_only IN BOOLEAN ) IS
2290 --
2291 l_proc VARCHAR2(72) := g_package||'populate_ghr_cpdf_temp';
2292 --
2293 l_info_type VARCHAR2(200) := NULL;
2294 l_api_assignment_id per_assignments.assignment_id%TYPE;
2295 --
2296 l_first_noa_id ghr_pa_requests.first_noa_id%TYPE;
2297 l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
2298 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2299 l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2300 l_noa_family_code ghr_pa_requests.noa_family_code%TYPE;
2301 l_multi_error_flag boolean;
2302 --
2303 -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
2304 -- selected by a user in the given period
2305 -- may as well select everyting from the PA Request table (saves going back!)
2306
2307 -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
2308 -- records based on business group id/security group id
2309 -- Not added outer join for per_people_f because we are expecting existence of person_id
2310 -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
2311
2312 -- 24-OCT-2002 JH truncated sf50_approval_date because it does contain time on the db.
2313 -- which causes some rows to not be included on last day.
2314
2315 -- Bug 4349372 changed per_people_f to per_all_people_f.
2316
2317 CURSOR cur_get_pars IS
2318 SELECT par.*
2319 FROM ghr_pa_requests par,
2320 per_all_people_f per
2321 WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
2322 AND par.person_id = per.person_id
2323 AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
2324 AND per.effective_end_date
2325 AND trunc(par.sf50_approval_date) BETWEEN p_start_date AND p_end_date
2326 --bug #6976546 removed 'FUTURE_ACTION'
2327 AND par.status IN ('UPDATE_HR_COMPLETE')
2328 AND par.effective_date >= add_months(p_end_date,-24)
2329 AND par.effective_date <= add_months(p_end_date,6)
2330 AND exclude_agency(NVL(par.agency_code,par.from_agency_code)) <> 'TRUE'
2331 AND exclude_noac(par.first_noa_code,par.second_noa_code,par.noa_family_code) <> 'TRUE'
2332 AND decode(hr_general.get_xbg_profile,'Y',per.business_group_id , hr_general.get_business_group_id) = per.business_group_id;
2333 --
2334 -- Cross Business Group if set to Yes, the pick all the records else pick BG related records.
2335 --
2336 -- Note:
2337 -- 1) The report calls this procedure in the before report trigger passing in the
2338 -- the two parameters the user actually passes in as agency code and subelement
2339 -- as one here called p_agency by concatanating the two and adding a %!!
2340 -- 2) The AGENCY_CODE field should actually be thought of as the TO_AGENCY_CODE
2341 -- field as it is only populated if there is a TO position otherwise the FROM_AGENCY_CODE
2342 -- will be populated. When update HR is succesful we must have one of these.
2343 -- Bug 706585 has been raised to make sure it gets populated on UPDATE_HR and not UPDATE_HR_COMPLETE
2344 -- otherwise this will not be available to FUTURE_ACTIONS
2345 -- 3) ordering of the records will be done in the report, by agency code then ssn
2346
2347 l_ghr_cpdf_temp_rec ghr_cpdf_temp%ROWTYPE;
2348 l_ghr_empty_cpdf_temp ghr_cpdf_temp%ROWTYPE;
2349 -- l_ghr_cpdf_cancel_rec ghr_cpdf_temp%ROWTYPE;
2350
2351 l_ghr_pa_requests_rec ghr_pa_requests%ROWTYPE;
2352 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
2353 l_retained_pay_plan ghr_pa_requests.to_pay_plan%type;
2354 l_retained_grade_or_level ghr_pa_requests.to_grade_or_level%type;
2355 l_retained_step_or_rate ghr_pa_requests.to_step_or_rate%type;
2356 l_sf52_rec1 ghr_pa_requests%ROWTYPE;
2357 l_sf52_rec2 ghr_pa_requests%ROWTYPE;
2358 l_dual_flg BOOLEAN:=FALSE;
2359 l_single_flg BOOLEAN:=TRUE;
2360 l_loop NUMBER :=1;
2361 l_index NUMBER :=1;
2362 --Bug#2789704
2363 l_log_text ghr_process_log.log_text%type;
2364 l_message_name ghr_process_log.message_name%type;
2365 l_log_date ghr_process_log.log_date%type;
2366
2367 -- Bug 5011003
2368 l_locality_pay_area_code ghr_locality_pay_areas_f.locality_pay_area_code%type;
2369 l_equiv_plan ghr_pay_plans.equivalent_pay_plan%type;
2370 -- End Bug 5011003
2371 -- the following cursor can be used for dual purposes
2372 -- can pass the exact eff date or the eff_date-1 to pick the prior details
2373 --
2374 CURSOR cur_per_details( p_person_id per_all_people_f.person_id%type,
2375 p_eff_date ghr_pa_requests.effective_date%TYPE)
2376 IS
2377 SELECT last_name, middle_names, first_name,title,business_group_id
2378 FROM per_all_people_f
2379 WHERE person_id = p_person_id
2380 AND p_eff_date between effective_start_date and effective_end_date;
2381
2382 -- Bug 5010844
2383 -- Cursor for getting authorization person details
2384 CURSOR cur_approver_id(c_pa_request_id ghr_pa_requests.pa_request_id%type)
2385 IS
2386 SELECT user_name_employee_id approver_id
2387 FROM ghr_pa_routing_history
2388 WHERE pa_request_id = c_pa_request_id
2389 AND approval_status = 'APPROVE'
2390 AND action_taken IN ('UPDATE_HR','FUTURE_ACTION');
2391
2392 l_approver_id ghr_pa_routing_history.user_name_employee_id%type;
2393 -- End Bug 5010844
2394
2395 -- Bug#5209089 Created the cursor.
2396 CURSOR cur_poid_approver (c_poid VARCHAR2)
2397 IS
2398 SELECT person_id
2399 FROM ghr_pois
2400 WHERE personnel_office_id = c_poid;
2401
2402 --
2403 -- This cursor can be used to pick the prior num, org details of that pos
2404 --
2405 CURSOR cur_prior_pos_org(p_position_id hr_positions_f.position_id%TYPE,
2406 p_eff_date ghr_pa_requests.effective_date%TYPE)
2407 IS
2408 SELECT name
2409 FROM hr_organization_units
2410 WHERE organization_id = ( SELECT organization_id
2411 FROM hr_positions_f
2412 WHERE position_id=p_position_id
2413 AND p_eff_date between effective_start_date and effective_end_date);
2414 --
2415 CURSOR cur_pos_org(p_org_id hr_organization_units.organization_id%TYPE,
2416 p_eff_date ghr_pa_requests.effective_date%TYPE)
2417 IS
2418 SELECT name
2419 FROM hr_organization_units
2420 WHERE organization_id = p_org_id
2421 AND p_eff_date between date_from and NVL(date_to,to_Date('31/12/4712','DD/MM/YYYY'));
2422 --
2423 -- added date cond and other fields for ENW EHRI changes
2424 --
2425 CURSOR cur_scd_dates(p_pa_request_id ghr_pa_requests.pa_request_id%type)
2426 IS
2427 SELECT REI_INFORMATION3 rif ,REI_INFORMATION8 ret
2428 FROM ghr_pa_request_extra_info parei
2429 WHERE parei.pa_request_id=p_pa_request_id
2430 AND parei.information_type='GHR_US_PAR_CHG_SCD';
2431
2432 --BUG# 6458070 -- ssn corrected issue
2433 cursor cur_ssn_corr(p_altered_pa_request_id ghr_pa_requests.altered_pa_request_id%type,
2434 p_to_national_identifier ghr_pa_requests.employee_national_identifier%type)
2435 is
2436 SELECT employee_national_identifier
2437 FROM ghr_pa_requests
2438 WHERE pa_request_id = p_altered_pa_request_id
2439 AND employee_national_identifier <> p_to_national_identifier;
2440
2441 l_records_found BOOLEAN;
2442 l_mesgbuff1 VARCHAR2(4000);
2443 l_scd_rif ghr_pa_request_extra_info.rei_information3%type;
2444 l_scd_retirement ghr_pa_request_extra_info.rei_information8%type;
2445 l_scd_tsp ghr_pa_request_extra_info.rei_information8%type;
2446 l_scd_leave ghr_pa_request_extra_info.rei_information8%type;
2447 l_ehri_id ghr_cpdf_temp.ehri_employee_id%TYPE;
2448 ll_per_ei_data per_people_extra_info%rowtype;
2449 l_last_name per_all_people.last_name%type;
2450 l_suffix ghr_cpdf_temp.prior_name_suffix%type;
2451
2452 -- For Dual Actions PRD is becoming null so preserving it using a local variable.
2453 l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
2454 --
2455 CURSOR cur_rem(p_pa_request_id ghr_pa_requests.pa_request_id%TYPE)
2456 IS
2457 SELECT Description
2458 FROM ghr_pa_remarks
2459 WHERE pa_request_id=p_pa_request_id;
2460
2461 l_dummy VARCHAR2(250);
2462 --
2463 -- Bug# 6158983
2464 l_value VARCHAR2(250);
2465 l_effective_start_date date;
2466 l_business_group_id per_all_people.business_group_id%type;
2467
2468 -- Bug# 6158983
2469
2470 BEGIN
2471 --
2472 l_records_found:=FALSE;
2473 -- hr_utility.trace_on(null,'venkat');
2474 hr_utility.set_location('Entering:'||l_proc, 10);
2475 --
2476 ghr_mto_int.set_log_program_name('ghr_ehri_dynrpt');
2477 --
2478 IF p_end_date > p_start_Date THEN
2479
2480 FOR cur_get_pars_rec IN cur_get_pars
2481 LOOP
2482 -- 1) Get PA Request data
2483 l_ghr_pa_requests_rec := cur_get_pars_rec;
2484
2485
2486 l_sf52_rec1 := l_ghr_pa_requests_rec;
2487 l_sf52_rec2 := l_ghr_pa_requests_rec;
2488
2489 IF l_ghr_pa_requests_rec.second_noa_code IS NOT NULL
2490 AND l_ghr_pa_requests_rec.first_noa_code NOT IN ('001','002') THEN
2491 l_loop := 2;
2492 l_dual_flg := TRUE;
2493 l_single_flg := FALSE;
2494 ELSE
2495 l_loop := 1;
2496 l_single_flg := TRUE;
2497 l_dual_flg := FALSE;
2498 END IF;
2499
2500 /* If ( l_ghr_pa_requests_rec.first_noa_code like '3%'and
2501 l_ghr_pa_requests_rec.second_noa_code ='825' ) THEN
2502 l_loop := 2;
2503 l_dual_flg := TRUE;
2504 l_single_flg := FALSE;
2505 ELSE
2506 l_loop := 1;
2507 l_single_flg := TRUE;
2508 l_dual_flg := FALSE;
2509 END IF;
2510 */
2511 --
2512 -- 2) Do any further checks to see if this PAR record should be included in the report:
2513 --
2514 FOR l_index in 1..l_loop
2515 LOOP
2516 BEGIN
2517 -- Loop twice for dual action
2518 IF ( l_dual_flg = TRUE and l_index = 1 ) then
2519
2520 ghr_process_sf52.assign_new_rg( p_action_num => 1,
2521 p_pa_req => l_sf52_rec1);
2522 l_ghr_pa_requests_rec := l_sf52_rec1;
2523 ELSIF ( l_dual_flg = TRUE and l_index = 2 ) then
2524 -- In case of Dual Actin assign_new_rg is nulling out the PRD.
2525 l_pay_rate_determinant := l_ghr_pa_requests_rec.pay_rate_determinant;
2526 ghr_process_sf52.assign_new_rg( p_action_num => 2,
2527 p_pa_req => l_sf52_rec2);
2528
2529 l_ghr_pa_requests_rec := l_sf52_rec2;
2530 l_ghr_pa_requests_rec.pay_rate_determinant := l_pay_rate_determinant;
2531 l_dual_flg := FALSE;
2532 ELSIF (l_single_flg = TRUE and l_dual_flg <> TRUE ) THEN
2533 l_ghr_pa_requests_rec := cur_get_pars_rec;
2534 END IF;
2535
2536 -- Bug# 4648811 getting the suffix from the lastname and also removing suffix from lastname
2537 get_suffix_lname(l_ghr_pa_requests_rec.employee_last_name,
2538 l_ghr_pa_requests_rec.effective_date,
2539 l_suffix,
2540 l_last_name);
2541
2542 l_ghr_pa_requests_rec.employee_last_name := l_last_name;
2543 --End Bug# 4648811
2544
2545 hr_utility.set_location(l_proc||' Check non_us_citizen_and_foreign_ds' ,30);
2546 --
2547 -- 2.1) Do not include PAR's for a non US Citizen in a foreign country
2548 IF non_us_citizen_and_foreign_ds (p_citizenship => l_ghr_pa_requests_rec.citizenship
2549 ,p_duty_station_code => l_ghr_pa_requests_rec.duty_station_code) THEN
2550 GOTO end_par_loop; -- loop for the next one!
2551 END IF;
2552 --
2553 hr_utility.set_location(l_proc||' Customer exclusion hook' ,40);
2554 --
2555 -- Bug 714944 -- Added exclusion of NAF:
2556 IF exclude_position (p_position_id => NVL(l_ghr_pa_requests_rec.to_position_id
2557 ,l_ghr_pa_requests_rec.from_position_id)
2558 ,p_effective_date => l_ghr_pa_requests_rec.effective_date) THEN
2559 GOTO end_par_loop; -- loop for the next one!
2560 END IF;
2561
2562 -- Obtain Retained Grade information
2563
2564 BEGIN
2565 l_retained_pay_plan := NULL;
2566 l_retained_grade_or_level := NULL;
2567 l_retained_step_or_rate := NULL;
2568 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
2569 p_person_id => l_ghr_pa_requests_rec.person_id,
2570 p_effective_date => l_ghr_pa_requests_rec.effective_date
2571 );
2572
2573 l_retained_pay_plan := l_retained_grade_rec.pay_plan;
2574 l_retained_grade_or_level := l_retained_grade_rec.grade_or_level;
2575 l_retained_step_or_rate := l_retained_grade_rec.step_or_rate;
2576 EXCEPTION
2577 WHEN ghr_pay_calc.pay_calc_message THEN
2578 NULL;
2579 END;
2580
2581 -- Emptying GHR_CPDF_TEMP_REC... Added for bug# 1375342
2582 l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
2583
2584 --
2585 -- 2.2) Add a cutomer hook to determine whether or not to include in report or not!!!!
2586 -- This maybe particuarly useful for excluding Non appropriated fund personnel (NAF) as currently
2587 -- we do not hold this infoamtion about a person or position, but apparently DoD hold it
2588 -- in the position kff
2589 --
2590 --
2591 -- 3) Now we have decided to keep this populate the ghr_cpdf_temp record group:
2592 -- First with all the information the PAR table itself and then go and get any more information needed
2593 -- If we are just doing a count, we do not need to do the second bit!
2594 --
2595 -- 3.1) Get all info from PAR table itself
2596 --
2597 hr_utility.set_location(l_proc||' populate cpdf temp from par' ,50);
2598 --
2599 l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
2600 -- Agency Subelement Code
2601 l_ghr_cpdf_temp_rec.to_national_identifier := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
2602 -- SSN
2603 -- SSN corrected
2604 IF (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2605 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '1%') THEN
2606 --Bug# 6458070 added the below cursor to check whether ssn is corrected
2607 --Bug# 6458070 added the below cursor to check whether ssn is corrected
2608 for cur_ssn_corr_rec in cur_ssn_corr(l_ghr_pa_requests_rec.altered_pa_request_id,
2609 l_ghr_pa_requests_rec.employee_national_identifier)
2610 loop
2611 l_ghr_cpdf_temp_rec.SSN_CORRECTED := format_ni(cur_ssn_corr_rec.employee_national_identifier);
2612 end loop;
2613 END IF;
2614 --
2615
2616 IF (l_ghr_pa_requests_rec.first_noa_code = '001' AND
2617 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '350') OR
2618 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2619 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') <> '355') OR
2620 (l_ghr_pa_requests_rec.first_noa_code NOT IN ('001', '002', '350', '355')) THEN
2621 IF l_ghr_pa_requests_rec.first_noa_code = '002' THEN
2622 l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.second_action_la_code1;
2623 l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.second_action_la_code2;
2624 ELSE
2625 l_ghr_cpdf_temp_rec.first_action_la_code1 := l_ghr_pa_requests_rec.first_action_la_code1;
2626 l_ghr_cpdf_temp_rec.first_action_la_code2 := l_ghr_pa_requests_rec.first_action_la_code2;
2627 END IF;
2628 END IF;
2629
2630 l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date; -- format in report
2631 l_ghr_cpdf_temp_rec.first_noa_code := format_noac(l_ghr_pa_requests_rec.first_noa_code);
2632 l_ghr_cpdf_temp_rec.second_noa_code := format_noac(l_ghr_pa_requests_rec.second_noa_code);-- Moved here for bug# 1399854
2633
2634 -- All NOACS need to report details regarding the approval date, authorizer details etc.
2635 l_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE := l_ghr_pa_requests_rec.APPROVAL_DATE;
2636
2637 -- 5010844
2638 -- Setting l_approver_id to NULL to find the proper approver's name.
2639 l_approver_id := NULL;
2640 FOR l_cur_auth_id IN cur_approver_id(l_ghr_pa_requests_rec.pa_request_id) LOOP
2641 l_approver_id := l_cur_auth_id.approver_id;
2642 END LOOP;
2643 -- Bug#5209089 If Approver's ID is NULL in the pa_routing, the action may be a mass action.
2644 -- For mass actions pick the approver's ID from the approver name attached to the Personnel Office.
2645 IF l_approver_id IS NULL THEN
2646 FOR l_approver_rec IN cur_poid_approver(l_ghr_pa_requests_rec.personnel_office_id)
2647 LOOP
2648 l_approver_id := l_approver_rec.person_id;
2649 END LOOP;
2650 END IF;
2651
2652 FOR auth_det IN cur_per_details(l_approver_id,
2653 l_ghr_pa_requests_rec.effective_date)
2654 LOOP
2655 -- End Bug 5010844
2656 -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
2657 get_suffix_lname(auth_det.last_name,
2658 l_ghr_pa_requests_rec.effective_date,
2659 l_suffix,
2660 l_last_name);
2661 l_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME := l_last_name;
2662 l_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME := auth_det.first_name;
2663 l_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME := auth_det.middle_names;
2664 l_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX := l_suffix;
2665 -- End Bug 4648811
2666
2667
2668 END LOOP;
2669
2670 l_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE := l_ghr_pa_requests_rec.sf50_approving_ofcl_work_title;
2671
2672 -- Restricting Remarks to 2000 characters.
2673 FOR rem_rec IN cur_rem(l_ghr_pa_requests_rec.pa_request_id) LOOP
2674 l_ghr_cpdf_temp_rec.remarks_text := SUBSTR(l_ghr_cpdf_temp_rec.remarks_text||rem_rec.description,1,2000);
2675 IF LENGTH(l_ghr_cpdf_temp_rec.remarks_text) = 2000 THEN
2676 EXIT;
2677 END IF;
2678 END LOOP;
2679 --Begin Bug# 5444553
2680 l_ghr_cpdf_temp_rec.remarks_text := REPLACE(l_ghr_cpdf_temp_rec.remarks_text,fnd_global.local_chr(10),' ');
2681 --End Bug# 5444553
2682
2683 ---
2684
2685 --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2686 ---
2687 -- IF Cancellation THEN no more data elements are needed. Bug# 1375323
2688 -- Insert_row in GHR_CPDF_TEMP, and continue in the LOOP for the next PAR row.
2689 --- EHRI_EMPLOYEE_ID IS TO BE REPORTED FOR ALL THE EMPLOYEES AND FOR ALL RPA's
2690 --- esp for 001 action
2691 BEGIN
2692 get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
2693 ,l_ghr_pa_requests_rec.first_noa_id
2694 ,l_ghr_pa_requests_rec.second_noa_id
2695 ,l_ghr_pa_requests_rec.noa_family_code
2696 ,l_ghr_pa_requests_rec.person_id
2697 ,l_ghr_pa_requests_rec.effective_date
2698 ,l_dummy
2699 ,l_dummy
2700 ,l_dummy
2701 ,l_dummy
2702 ,l_dummy
2703 ,l_dummy
2704 ,l_dummy
2705 -- Added for new EHRI changes Madhuri 21-Jan-2005
2706 ,l_dummy
2707 ,l_dummy
2708 ,l_dummy
2709 ,l_ghr_cpdf_temp_rec.ehri_employee_id
2710 ,l_dummy
2711 ,l_dummy
2712 ,l_dummy
2713 ,l_dummy
2714 ,l_dummy
2715 ,l_dummy
2716 ,l_dummy
2717 ,l_dummy
2718 ,l_dummy
2719 ,l_dummy
2720 ,l_dummy
2721 ,l_dummy
2722 ,l_dummy
2723 ,l_dummy
2724 ,l_dummy
2725 ,l_dummy
2726 ,l_dummy
2727 ,l_dummy
2728 ,l_dummy
2729 ,l_dummy
2730 ,l_dummy
2731 ,l_dummy);
2732
2733 EXCEPTION
2734 WHEN OTHERS THEN
2735 l_message_name := 'get_par_ei_noac';
2736 l_log_text := 'Error in fetching EHRI Employee id for pa_request_id: '||
2737 l_ghr_pa_requests_rec.pa_request_id ||
2738 ' ; SSN/employee last name' ||
2739 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2740 l_ghr_pa_Requests_rec.employee_last_name ||
2741 ' ; first NOAC/Second NOAC: '||
2742 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2743 l_ghr_pa_requests_rec.second_noa_code ||
2744 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2745
2746 Raise EHRI_DYNRPT_ERROR;
2747 END;
2748
2749 -- Bug 5063301
2750 --Birth Date
2751 l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2752 -- End Bug 5063301
2753
2754 IF l_ghr_pa_requests_rec.first_noa_code = '001' THEN
2755 insert_row(l_ghr_cpdf_temp_rec);
2756 l_records_found:=TRUE;
2757 GOTO end_par_loop; -- loop for the next one!
2758 END IF;
2759
2760 -- Obtain Family Code
2761 l_noa_family_code := l_ghr_pa_requests_rec.noa_family_code;
2762 IF l_noa_family_code = 'CORRECT' THEN
2763 -- Bug#2789704 Added Exception Handling
2764 -- Bug#5172710 Modified the function to determine the noa family code.
2765 BEGIN
2766 l_noa_family_code := ghr_pa_requests_pkg.get_noa_pm_family
2767 (l_ghr_pa_requests_rec.second_noa_id,
2768 l_ghr_pa_requests_rec.effective_date);
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 l_message_name := 'get_family_code';
2772 l_log_text := 'Error in getting family code for pa_request_id: '||
2773 l_ghr_pa_requests_rec.pa_request_id ||
2774 ' ; SSN/employee last name' ||
2775 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2776 l_ghr_pa_Requests_rec.employee_last_name ||
2777 ' ; first NOAC/Second NOAC: '||
2778 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2779 l_ghr_pa_requests_rec.second_noa_code ||
2780 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2781
2782 Raise EHRI_DYNRPT_ERROR;
2783 END;
2784 -- Bug#2789704 Added Exception Handling
2785 END IF;
2786
2787 -- Moved POI to this place for bug# 1402287 to not print for Cancellations.
2788 l_ghr_cpdf_temp_rec.personnel_office_id := l_ghr_pa_requests_rec.personnel_office_id;
2789 -- Personnel Office Identifier Code
2790 l_ghr_cpdf_temp_rec.employee_date_of_birth := l_ghr_pa_requests_rec.employee_date_of_birth; -- format in report
2791 --Birth Date
2792 l_ghr_cpdf_temp_rec.FEGLI := l_ghr_pa_requests_rec.FEGLI;
2793 l_ghr_cpdf_temp_rec.annuitant_indicator := l_ghr_pa_requests_rec.annuitant_indicator;
2794 l_ghr_cpdf_temp_rec.veterans_preference := l_ghr_pa_requests_rec.veterans_preference;
2795 l_ghr_cpdf_temp_rec.tenure := l_ghr_pa_requests_rec.tenure;
2796 l_ghr_cpdf_temp_rec.service_comp_date := l_ghr_pa_requests_rec.service_comp_date; -- format in report
2797 l_ghr_cpdf_temp_rec.retirement_plan := l_ghr_pa_requests_rec.retirement_plan;
2798 -- Retirement System Type Code (retierment plan)
2799
2800
2801 --Start of Bug #6158983
2802 --start of Bug #6522440 adding one more validation of showing the hyp_full_reg_duty_part_emp
2803 -- only for part time employees
2804 if l_ghr_pa_requests_rec.part_time_hours is not null then
2805 if l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','T') then
2806 l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 72.00;
2807 else
2808 l_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp := 80.00;
2809 end if;
2810 end if;
2811
2812 -- End of Bug #6158983
2813
2814
2815
2816
2817 l_ghr_cpdf_temp_rec.veterans_status := l_ghr_pa_requests_rec.veterans_status;
2818 l_ghr_cpdf_temp_rec.FLSA_category := l_ghr_pa_requests_rec.FLSA_category;
2819
2820 IF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF in ('P','R') ) THEN
2821 l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'Y';
2822 ELSIF (l_ghr_pa_requests_rec.VETERANS_PREF_FOR_RIF = 'N') THEN
2823 l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'N';
2824 ELSE
2825 l_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF := 'NA';
2826 END IF;
2827
2828 -- APPOINTMENT_NTE_DATE
2829 BEGIN
2830
2831 get_asg_details( l_ghr_pa_requests_rec.pa_request_id,
2832 l_ghr_pa_requests_rec.person_id,
2833 l_ghr_pa_requests_rec.effective_date,
2834 l_ghr_cpdf_temp_rec.appointment_nte_date);
2835
2836 EXCEPTION
2837 WHEN OTHERS THEN
2838 l_message_name := 'get_asg_details';
2839 l_log_text := 'Error in getting appointment_nte_date for pa_request_id: '||
2840 l_ghr_pa_requests_rec.pa_request_id ||
2841 ' ; SSN/employee last name' ||
2842 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
2843 l_ghr_pa_Requests_rec.employee_last_name ||
2844 ' ; first NOAC/Second NOAC: '||
2845 l_ghr_pa_requests_rec.first_noa_code || ' / '||
2846 l_ghr_pa_requests_rec.second_noa_code ||
2847 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2848
2849 Raise EHRI_DYNRPT_ERROR;
2850 END;
2851
2852 --
2853 --Bug#5328177 Added the following conditions to report the incentive percentage, amount
2854 -- in the Award percentage(for noac 827) and award amount(for noacs 815,816,825) fields.
2855 IF l_ghr_pa_requests_rec.first_noa_code = '827' OR
2856 l_ghr_pa_requests_rec.second_noa_code = '827' THEN
2857 l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.to_total_salary;
2858 ELSIF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
2859 l_ghr_pa_requests_rec.second_noa_code IN ('815','816','825')) AND
2860 l_ghr_pa_requests_rec.award_amount IS NULL THEN
2861 l_ghr_pa_requests_rec.award_amount := l_ghr_pa_requests_rec.to_total_salary;
2862 END IF;
2863
2864
2865 -- IF l_noa_family_code = 'AWARD' THEN --BUG#5328177
2866 IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
2867
2868 l_ghr_pa_requests_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2869 l_ghr_pa_requests_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2870 l_ghr_pa_requests_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2871 l_ghr_pa_requests_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2872 l_ghr_pa_requests_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay;
2873 l_ghr_pa_requests_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2874 l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
2875 l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2876 l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2877
2878 END IF;
2879
2880 l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.to_pay_plan;
2881 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2882 l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.to_grade_or_level;
2883 l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.to_step_or_rate;
2884 l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.to_basic_pay; -- format in report
2885 l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.to_pay_basis;
2886 l_ghr_cpdf_temp_rec.to_locality_adj := l_ghr_pa_requests_rec.to_locality_adj;
2887 l_ghr_cpdf_temp_rec.to_total_salary := l_ghr_pa_requests_rec.to_total_salary; -- format in report
2888 l_ghr_cpdf_temp_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.to_adj_basic_pay;
2889
2890
2891 IF l_noa_family_code <> 'AWARD' THEN
2892 -- Added following 'IF' according to bug# 1375333
2893 IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2894 l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2895 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2896 (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2897 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2898 )
2899 ) THEN
2900 l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
2901 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.to_occ_code;
2902 ELSE
2903 l_ghr_cpdf_temp_rec.to_pay_rate_determinant := NULL;
2904 l_ghr_cpdf_temp_rec.to_occ_code := NULL;
2905 --
2906 l_ghr_cpdf_temp_rec.to_pay_plan := NULL;
2907 l_ghr_cpdf_temp_rec.to_occ_code := NULL;
2908 l_ghr_cpdf_temp_rec.to_grade_or_level := NULL;
2909 l_ghr_cpdf_temp_rec.to_step_or_rate := NULL;
2910 l_ghr_cpdf_temp_rec.to_basic_pay := NULL;
2911 l_ghr_cpdf_temp_rec.to_pay_basis := NULL;
2912 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
2913 l_ghr_cpdf_temp_rec.to_total_salary := NULL;
2914 l_ghr_cpdf_temp_rec.to_adj_basic_pay := NULL;
2915 END IF;
2916 END IF;
2917
2918 -- Added following 'IF' according to bug# 1375333
2919 IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
2920 l_ghr_pa_requests_rec.first_noa_code LIKE '4%' OR
2921 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
2922 (NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%' OR
2923 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%')
2924 )
2925 ) THEN
2926
2927
2928 l_ghr_cpdf_temp_rec.to_work_schedule := l_ghr_pa_requests_rec.work_schedule;
2929 l_ghr_cpdf_temp_rec.part_time_hours := l_ghr_pa_requests_rec.part_time_hours;
2930 --- commented for bug# 2257630 as duty station code required for all NOA codes except for cancellation action
2931 -- l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2932
2933 ELSE
2934 l_ghr_cpdf_temp_rec.to_work_schedule := NULL;
2935 l_ghr_cpdf_temp_rec.part_time_hours := NULL;
2936 END IF;
2937
2938 --Start of BUG# 6631879
2939
2940 IF l_ghr_cpdf_temp_rec.to_work_schedule in ('I','J') then
2941 l_ghr_cpdf_temp_rec.part_time_hours := NULL;
2942 ELSIF l_ghr_cpdf_temp_rec.to_work_schedule in ('F','G','B') then
2943 IF l_ghr_cpdf_temp_rec.retirement_plan in ('E','M','T') then
2944 l_ghr_cpdf_temp_rec.part_time_hours := 144;
2945 ELSE
2946 l_ghr_cpdf_temp_rec.part_time_hours := 80;
2947 END IF;
2948 END IF;
2949 --End of BUG# 6631879
2950
2951 l_ghr_cpdf_temp_rec.to_duty_station_code := format_ds(l_ghr_pa_requests_rec.duty_station_code);
2952 l_ghr_cpdf_temp_rec.position_occupied := l_ghr_pa_requests_rec.position_occupied;
2953 l_ghr_cpdf_temp_rec.supervisory_status := l_ghr_pa_requests_rec.supervisory_status;
2954 -- Supervisory Status
2955 l_ghr_cpdf_temp_rec.award_amount := l_ghr_pa_requests_rec.award_amount; -- format in report
2956
2957 -- Added IF for bug# 1375342
2958 IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
2959 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','825','826','827',
2960 '840','841','842','843','844','845','846','847','848','849','878','879')
2961 )
2962 OR
2963 l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','825','826','827',
2964 '840','841','842','843','844','845','846','847','848','849','878','879')
2965 )
2966 AND
2967 NOT ( (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' ) OR
2968 (l_ghr_pa_requests_rec.first_noa_code ='002' and
2969 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%'
2970 )
2971 )
2972 THEN
2973
2974 l_ghr_cpdf_temp_rec.from_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
2975 l_ghr_cpdf_temp_rec.from_occ_code := l_ghr_pa_requests_rec.from_occ_code;
2976 l_ghr_cpdf_temp_rec.from_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
2977 l_ghr_cpdf_temp_rec.from_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
2978 l_ghr_cpdf_temp_rec.from_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
2979 l_ghr_cpdf_temp_rec.from_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
2980 l_ghr_cpdf_temp_rec.from_total_salary := l_ghr_pa_requests_rec.from_total_salary;
2981 l_ghr_cpdf_temp_rec.from_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
2982 END IF;
2983
2984 -- NEW EHRI changes need these prior details
2985 -- Madhuri
2986
2987 IF get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
2988 p_effective_date => l_ghr_pa_requests_rec.effective_date) <> '99' THEN
2989
2990 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
2991 l_ghr_pa_requests_rec.first_noa_code NOT LIKE '1%' AND
2992 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '2%' AND
2993 (NVL(l_ghr_pa_requests_rec.first_noa_code,'@#') <> '002' OR
2994 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') NOT LIKE '2%'
2995 )
2996 ) THEN
2997
2998 IF ( l_noa_family_code <> 'AWARD' or
2999 (l_ghr_pa_requests_rec.first_noa_code ='885' or
3000 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')='885') )
3001 THEN
3002 --
3003 IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.from_pay_plan)) <> 'FW' THEN
3004 l_ghr_cpdf_temp_rec.from_locality_adj := NVL(l_ghr_pa_requests_rec.from_locality_adj, 0);
3005 ELSE
3006 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3007 END IF;
3008 --
3009 END IF;
3010 ELSE
3011 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3012 END IF;
3013
3014 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3015 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '3%' AND
3016 (l_ghr_pa_requests_rec.first_noa_code <> '002' OR
3017 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '3%'
3018 )
3019 ) AND
3020 (l_ghr_pa_requests_rec.first_noa_code NOT LIKE '4%' AND
3021 (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3022 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') NOT LIKE '4%'
3023 )
3024 ) THEN
3025 IF get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan)) <> 'FW' THEN
3026 l_ghr_cpdf_temp_rec.to_locality_adj := NVL(l_ghr_pa_requests_rec.to_locality_adj, 0);
3027 END IF;
3028 ELSE
3029 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3030 END IF;
3031 ELSE
3032 -- 4163587 Loc pay is not reported for prior loc pay
3033 -- NO NEED TO MAKE PRIOR LOC PAY ADJ NULL, as we are checking for current DS not prior DS
3034 -- l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3035 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3036 END IF;
3037
3038 IF NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' OR
3039 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3040 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3041 )
3042 ) AND
3043 NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3044 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3045 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3046 )
3047 )
3048 THEN -- Issue 20 4257213
3049 l_ghr_cpdf_temp_rec.to_staffing_differential := l_ghr_pa_requests_rec.to_staffing_differential; -- format in report
3050 l_ghr_cpdf_temp_rec.to_supervisory_differential := l_ghr_pa_requests_rec.to_supervisory_differential;-- format in report
3051 l_ghr_cpdf_temp_rec.to_retention_allowance := l_ghr_pa_requests_rec.to_retention_allowance; -- format in report
3052 ELSE
3053 l_ghr_cpdf_temp_rec.to_staffing_differential := NULL;
3054 l_ghr_cpdf_temp_rec.to_supervisory_differential := NULL;
3055 l_ghr_cpdf_temp_rec.to_retention_allowance := NULL;
3056 END IF;
3057
3058 IF l_noa_family_code IN ('AWARD', 'OTHER_PAY','GHR_INCENTIVE') THEN -- Bug# 1400486 --GHR_INCENTIVE added for bug # 5328177
3059 IF l_ghr_pa_requests_rec.first_noa_code IN ('818', '819') OR
3060 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3061 l_ghr_pa_requests_rec.second_noa_code IN ('818', '819')) THEN
3062 IF l_ghr_cpdf_temp_rec.award_amount IS NULL THEN
3063 -- Bug# 1494916. By ENUNEZ. From 10.7 Dec2000 Patch release
3064 IF l_ghr_pa_requests_rec.first_noa_code = '818' OR
3065 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3066 l_ghr_pa_requests_rec.second_noa_code = '818') THEN
3067 -- Bug#2789704 Added Exception Handling
3068 BEGIN
3069 ghr_api.retrieve_element_entry_value (p_element_name => 'AUO'
3070 ,p_input_value_name => 'Amount'
3071 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3072 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3073 ,p_value => l_ghr_cpdf_temp_rec.award_amount
3074 ,p_multiple_error_flag => l_multi_error_flag);
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 l_message_name := 'retrieve_element_entry_value';
3078 l_log_text := 'Error in fetching AUO Amount for pa_request_id: '||
3079 l_ghr_pa_requests_rec.pa_request_id ||
3080 ' ; SSN/employee last name' ||
3081 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3082 l_ghr_pa_Requests_rec.employee_last_name ||
3083 ' ; first NOAC/Second NOAC: '||
3084 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3085 l_ghr_pa_requests_rec.second_noa_code ||
3086 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3087
3088 Raise EHRI_DYNRPT_ERROR;
3089 END;
3090 -- Bug#2789704 Added Exception Handling
3091 ELSE
3092 -- Bug#2789704 Added Exception Handling
3093 BEGIN
3094 ghr_api.retrieve_element_entry_value (p_element_name => 'Availability Pay'
3095 ,p_input_value_name => 'Amount'
3096 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3097 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3098 ,p_value => l_ghr_cpdf_temp_rec.award_amount
3099 ,p_multiple_error_flag => l_multi_error_flag);
3100 EXCEPTION
3101 WHEN OTHERS THEN
3102 l_message_name := 'retrieve_element_entry_value';
3103 l_log_text := 'Error in fetching Availability Pay Amount for pa_request_id: '||
3104 l_ghr_pa_requests_rec.pa_request_id ||
3105 ' ; SSN/employee last name' ||
3106 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3107 l_ghr_pa_Requests_rec.employee_last_name ||
3108 ' ; first NOAC/Second NOAC: '||
3109 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3110 l_ghr_pa_requests_rec.second_noa_code ||
3111 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3112
3113 Raise EHRI_DYNRPT_ERROR;
3114 END;
3115 -- Bug#2789704 Added Exception Handling
3116 END IF;
3117 END IF;
3118 ELSE
3119 IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
3120 -- Bug#2789704 Added Exception Handling
3121 BEGIN
3122 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3123 ,p_input_value_name => 'Amount'
3124 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3125 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3126 ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3127 ,p_multiple_error_flag => l_multi_error_flag);
3128 EXCEPTION
3129 WHEN OTHERS THEN
3130 l_message_name := 'retrieve_element_entry_value';
3131 l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3132 l_ghr_pa_requests_rec.pa_request_id ||
3133 ' ; SSN/employee last name' ||
3134 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3135 l_ghr_pa_Requests_rec.employee_last_name ||
3136 ' ; first NOAC/Second NOAC: '||
3137 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3138 l_ghr_pa_requests_rec.second_noa_code ||
3139 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3140
3141 Raise EHRI_DYNRPT_ERROR;
3142 END;
3143 -- Bug#2789704 Added Exception Handling
3144
3145 END IF;
3146 IF l_ghr_cpdf_temp_rec.to_retention_allowance IS NULL THEN
3147 -- Bug#2789704 Added Exception Handling
3148 BEGIN
3149 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3150 ,p_input_value_name => 'Amount'
3151 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3152 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3153 ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3154 ,p_multiple_error_flag => l_multi_error_flag);
3155 EXCEPTION
3156 WHEN OTHERS THEN
3157 l_message_name := 'retrieve_element_entry_value';
3158 l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3159 l_ghr_pa_requests_rec.pa_request_id ||
3160 ' ; SSN/employee last name' ||
3161 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3162 l_ghr_pa_Requests_rec.employee_last_name ||
3163 ' ; first NOAC/Second NOAC: '||
3164 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3165 l_ghr_pa_requests_rec.second_noa_code ||
3166 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3167
3168 Raise EHRI_DYNRPT_ERROR;
3169 END;
3170 -- Bug#2789704 Added Exception Handling
3171 END IF;
3172 END IF;
3173 END IF;
3174
3175 -- Sundar Changes for education
3176 ghr_api.return_education_details(p_person_id => l_ghr_pa_requests_rec.person_id,
3177 p_effective_date => l_ghr_pa_requests_rec.effective_date,
3178 p_education_level => l_ghr_cpdf_temp_rec.education_level,
3179 p_academic_discipline => l_ghr_cpdf_temp_rec.academic_discipline,
3180 p_year_degree_attained => l_ghr_cpdf_temp_rec.year_degree_attained);
3181 -- End Sundar changes for education Commented below code
3182
3183 -- Not worth getting any more detials if only counting!
3184 -- IF not p_count_only THEN
3185 --
3186 -- 3.2) Get Orgnaizational Component (Otherwise refered to as Org Structure ID)
3187 -- Since this appears to be required for all NOA's reported in the dynamic report
3188 -- it must come from to_position if there, if not from the from_position
3189 --
3190 -- Bug#2789704 Added Exception Handling
3191 BEGIN
3192 get_org_comp (NVL(l_ghr_pa_requests_rec.to_position_id
3193 ,l_ghr_pa_requests_rec.from_position_id)
3194 ,l_ghr_pa_requests_rec.effective_date
3195 ,l_ghr_cpdf_temp_rec.organizational_component);
3196 -- Organizational Component Code
3197 EXCEPTION
3198 WHEN OTHERS THEN
3199 l_message_name := 'get_org_comp';
3200 l_log_text := 'Error in fetching OPM Organizational Component for pa_request_id: '||
3201 l_ghr_pa_requests_rec.pa_request_id ||
3202 ' ; SSN/employee last name' ||
3203 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3204 l_ghr_pa_Requests_rec.employee_last_name ||
3205 ' ; first NOAC/Second NOAC: '||
3206 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3207 l_ghr_pa_requests_rec.second_noa_code ||
3208 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3209
3210 Raise EHRI_DYNRPT_ERROR;
3211 END;
3212 -- Getting appropriation code new EHRI CHanges.
3213 BEGIN
3214 get_appr_code(NVL(l_ghr_pa_requests_rec.to_position_id
3215 ,l_ghr_pa_requests_rec.from_position_id)
3216 ,l_ghr_pa_requests_rec.effective_date
3217 ,l_ghr_cpdf_temp_rec.APPROPRIATION_CODE);
3218 -- Organizational Component Code
3219 EXCEPTION
3220 WHEN OTHERS THEN
3221 l_message_name := 'get_appr_code';
3222 l_log_text := 'Error in fetching Appropriation code for the position of : '||
3223 l_ghr_pa_requests_rec.pa_request_id ||
3224 ' ; SSN/employee last name' ||
3225 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3226 l_ghr_pa_Requests_rec.employee_last_name ||
3227 ' ; first NOAC/Second NOAC: '||
3228 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3229 l_ghr_pa_requests_rec.second_noa_code ||
3230 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3231
3232 Raise EHRI_DYNRPT_ERROR;
3233 END;
3234 -- Bug#2789704 Added Exception Handling
3235 --
3236 -- 3.3) Get Sex
3237 -- Bug#2789704 Added Exception Handling
3238 BEGIN
3239 get_sex (l_ghr_pa_requests_rec.person_id
3240 ,l_ghr_pa_requests_rec.effective_date
3241 ,l_ghr_cpdf_temp_rec.sex);
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 l_message_name := 'get_sex';
3245 l_log_text := 'Error in fetching SEX for pa_request_id: '||
3246 l_ghr_pa_requests_rec.pa_request_id ||
3247 ' ; SSN/employee last name' ||
3248 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3249 l_ghr_pa_Requests_rec.employee_last_name ||
3250 ' ; first NOAC/Second NOAC: '||
3251 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3252 l_ghr_pa_requests_rec.second_noa_code ||
3253 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3254
3255 Raise EHRI_DYNRPT_ERROR;
3256 END;
3257 -- Bug#2789704 Added Exception Handling
3258 --
3259 -- 3.4) Get person SIT - 'US Fed Perf Appraisal'
3260 --
3261 -- Bug#2789704 Added Exception Handling
3262 BEGIN
3263 get_per_sit_perf_appraisal(l_ghr_pa_requests_rec.person_id
3264 ,l_ghr_pa_requests_rec.effective_date
3265 ,l_ghr_cpdf_temp_rec.rating_of_record_level
3266 ,l_ghr_cpdf_temp_rec.rating_of_record_pattern
3267 ,l_ghr_cpdf_temp_rec.rating_of_record_period_ends
3268 ,l_ghr_cpdf_temp_rec.rating_of_record_period_starts); -- format in report
3269 EXCEPTION
3270 WHEN OTHERS THEN
3271 l_message_name := 'get_per_sit_perf_apprisal';
3272 l_log_text := 'Error in fetching Performance Apprisal details for pa_request_id: '||
3273 to_char(l_ghr_pa_requests_rec.pa_request_id) ||
3274 ' ; SSN/employee last name' ||
3275 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3276 l_ghr_pa_Requests_rec.employee_last_name ||
3277 ' ; first NOAC/Second NOAC: '||
3278 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3279 l_ghr_pa_requests_rec.second_noa_code ||
3280 ' ; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3281 Raise EHRI_DYNRPT_ERROR;
3282
3283 END;
3284 -- Bug#2789704 Added Exception Handling
3285 --
3286 -- 3.5) Get PAR Extra Info Noa specific
3287 --
3288 -- Bug#2789704 Added Exception Handling
3289 BEGIN
3290 get_PAR_EI_noac (l_ghr_pa_requests_rec.pa_request_id
3291 ,l_ghr_pa_requests_rec.first_noa_id
3292 ,l_ghr_pa_requests_rec.second_noa_id
3293 ,l_ghr_pa_requests_rec.noa_family_code
3294 ,l_ghr_pa_requests_rec.person_id
3295 ,l_ghr_pa_requests_rec.effective_date
3296 ,l_ghr_cpdf_temp_rec.creditable_military_service -- no format assumed yymm?
3297 ,l_ghr_cpdf_temp_rec.frozen_service -- no format assumed yymm?
3298 ,l_ghr_cpdf_temp_rec.from_retirement_coverage -- previous retirement coverage
3299 ,l_ghr_cpdf_temp_rec.race_national_origin
3300 ,l_ghr_cpdf_temp_rec.handicap_code
3301 ,l_ghr_cpdf_temp_rec.individual_group_award -- format in report
3302 ,l_ghr_cpdf_temp_rec.benefit_amount -- format in report
3303 -- Added for new EHRI changes Madhuri 21-Jan-2005
3304 ,l_ghr_cpdf_temp_rec.leave_scd
3305 ,l_ghr_cpdf_temp_rec.scd_ses
3306 ,l_ghr_cpdf_temp_rec.scd_spcl_retire
3307 ,l_ghr_cpdf_temp_rec.ehri_employee_id
3308 ,l_ghr_cpdf_temp_rec.tsp_scd
3309 ,l_ghr_cpdf_temp_rec.scd_rif
3310 ,l_ghr_cpdf_temp_rec.scd_retirement
3311 ,l_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD
3312 ,l_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD
3313 ,l_ghr_cpdf_temp_rec.AGENCY_DATA1
3314 ,l_ghr_cpdf_temp_rec.AGENCY_DATA2
3315 ,l_ghr_cpdf_temp_rec.AGENCY_DATA3
3316 ,l_ghr_cpdf_temp_rec.AGENCY_DATA4
3317 ,l_ghr_cpdf_temp_rec.AGENCY_DATA5
3318 ,l_ghr_cpdf_temp_rec.race_ethnic_info
3319 ,l_ghr_cpdf_temp_rec.world_citizenship
3320 ,l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE -- 6312144 RPA - EIT Benefits related modifications
3321 ,l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS
3322 ,l_ghr_cpdf_temp_rec.FERS_EXC_APPTS
3323 ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1
3324 ,l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2
3325 ,l_ghr_cpdf_temp_rec.fegli_assg_indicator
3326 ,l_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt
3327 ,l_ghr_cpdf_temp_rec.fegli_court_ord_ind
3328 ,l_ghr_cpdf_temp_rec.fegli_benf_desg_ind
3329 ,l_ghr_cpdf_temp_rec.fehb_event_code ); -- Bug 4724337 Race or National Origin changes
3330 --Bug#6158983 World Citizenship
3331 l_ehri_id := l_ghr_cpdf_temp_rec.ehri_employee_id;
3332
3333
3334 EXCEPTION
3335 WHEN OTHERS THEN
3336 l_message_name := 'get_par_ei_noac';
3337 l_log_text := 'Error in fetching PA Record Extra Information for pa_request_id: '||
3338 l_ghr_pa_requests_rec.pa_request_id ||
3339 ' ; SSN/employee last name' ||
3340 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3341 l_ghr_pa_Requests_rec.employee_last_name ||
3342 ' ; first NOAC/Second NOAC: '||
3343 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3344 l_ghr_pa_requests_rec.second_noa_code ||
3345 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3346
3347 Raise EHRI_DYNRPT_ERROR;
3348 END;
3349 -- Bug#2789704 Added Exception Handling
3350
3351 -- Bug# 1375342
3352 IF (l_ghr_pa_requests_rec.first_noa_code LIKE '2%' OR
3353 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3354 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')) THEN
3355
3356 l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3357 l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3358 l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3359 l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3360 l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3361 l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3362 l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3363 l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3364 l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3365 l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3366 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3367
3368 -- for bug 3327389
3369 END IF;
3370
3371 -- get current appointment auth codes.
3372 -- Bug#2789704 Added Exception Handling
3373 BEGIN
3374 ghr_sf52_pre_update.get_auth_codes
3375 (p_pa_req_rec => l_ghr_pa_requests_rec
3376 ,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
3377 ,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
3378 EXCEPTION
3379 WHEN OTHERS THEN
3380 l_message_name := 'get_auth_codes';
3381 l_log_text := 'Error in fetching Current Appointment Authority Codes for pa_request_id: '||
3382 l_ghr_pa_requests_rec.pa_request_id ||
3383 ' ; SSN/employee last name' ||
3384 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3385 l_ghr_pa_Requests_rec.employee_last_name ||
3386 ' ; first NOAC/Second NOAC: '||
3387 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3388 l_ghr_pa_requests_rec.second_noa_code ||
3389 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3390
3391 Raise EHRI_DYNRPT_ERROR;
3392 END;
3393 -- Bug#2789704 Added Exception Handling
3394 --
3395 -- 3.6) Get PRIOR Work Schedule and Pay Rate Determinant
3396 --
3397 IF NOT ( (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3398 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '2%')
3399 OR
3400 l_ghr_pa_requests_rec.first_noa_code LIKE '2%'
3401 )
3402 THEN
3403
3404 -- Bug#2789704 Added Exception Handling
3405 BEGIN
3406 get_prior_ws_prd_ds (l_ghr_pa_requests_rec.pa_request_id
3407 ,l_ghr_pa_requests_rec.altered_pa_request_id
3408 ,l_ghr_pa_requests_rec.first_noa_id
3409 ,l_ghr_pa_requests_rec.second_noa_id
3410 ,l_ghr_pa_requests_rec.person_id
3411 ,l_ghr_pa_requests_rec.employee_assignment_id
3412 ,l_ghr_pa_requests_rec.from_position_id
3413 ,l_ghr_pa_requests_rec.effective_date
3414 ,l_ghr_pa_requests_rec.status
3415 ,l_ghr_cpdf_temp_rec.from_work_schedule
3416 ,l_ghr_cpdf_temp_rec.from_pay_rate_determinant
3417 ,l_ghr_cpdf_temp_rec.from_duty_station_code);
3418
3419 IF (l_ghr_pa_requests_rec.first_noa_code IN ('817') OR
3420 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3421 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#')= '817')
3422 )
3423 THEN
3424 l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3425 l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3426 l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3427 END IF;
3428
3429 EXCEPTION
3430 WHEN OTHERS THEN
3431 l_message_name := 'get_prior_ws_prd_ds';
3432 l_log_text := 'Error in fetching prior work schedule,prg,duty station for pa_request_id: '||
3433 l_ghr_pa_requests_rec.pa_request_id ||
3434 ' ; SSN/employee last name' ||
3435 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3436 l_ghr_pa_Requests_rec.employee_last_name ||
3437 ' ; first NOAC/Second NOAC: '||
3438 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3439 l_ghr_pa_requests_rec.second_noa_code ||
3440 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3441
3442 Raise EHRI_DYNRPT_ERROR;
3443 END;
3444
3445 -- bug#5328177 IF l_noa_family_code = 'AWARD' THEN
3446 IF l_noa_family_code IN ('AWARD','GHR_INCENTIVE') THEN
3447 l_ghr_cpdf_temp_rec.to_pay_rate_determinant := l_ghr_cpdf_temp_rec.from_pay_rate_determinant;
3448 l_ghr_cpdf_temp_rec.from_work_schedule := NULL;
3449 l_ghr_cpdf_temp_rec.from_pay_rate_determinant := NULL;
3450 l_ghr_cpdf_temp_rec.from_duty_station_code := NULL;
3451
3452 -- NULL OUT THESE FIELDS FOR AWARDS
3453 l_ghr_cpdf_temp_rec.from_pay_plan := NULL;
3454 l_ghr_cpdf_temp_rec.from_occ_code := NULL;
3455 l_ghr_cpdf_temp_rec.from_grade_or_level := NULL;
3456 l_ghr_cpdf_temp_rec.from_step_or_rate := NULL;
3457 l_ghr_cpdf_temp_rec.from_basic_pay := NULL; -- format in report
3458 l_ghr_cpdf_temp_rec.from_pay_basis := NULL;
3459 l_ghr_cpdf_temp_rec.from_total_salary := NULL;
3460 l_ghr_cpdf_temp_rec.from_adj_basic_pay := NULL;
3461
3462 END IF;
3463 -- Added IF Condition to fix bug#3231946
3464 IF get_loc_pay_area_code(p_duty_station_code => l_ghr_cpdf_temp_rec.from_duty_station_code,
3465 p_effective_date => l_ghr_pa_requests_rec.effective_date) = '99'
3466 AND l_ghr_pa_requests_rec.from_locality_adj = 0 THEN
3467 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3468 END IF;
3469 END IF;
3470 --
3471 -- 3.7) Get prior ssn if it is being corrected.
3472 --
3473 -- Bug#2789704 Added Exception Handling
3474 BEGIN
3475 get_prev_ssn (l_ghr_pa_requests_rec.altered_pa_request_id
3476 ,l_ghr_pa_requests_rec.employee_national_identifier
3477 ,l_ghr_pa_requests_rec.noa_family_code
3478 ,l_ghr_cpdf_temp_rec.from_national_identifier);
3479 EXCEPTION
3480 WHEN OTHERS THEN
3481 l_message_name := 'get_prev_ssn';
3482 l_log_text := 'Error in fetching SSN for pa_request_id: '||
3483 l_ghr_pa_requests_rec.pa_request_id ||
3484 ' ; SSN/employee last name' ||
3485 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3486 l_ghr_pa_Requests_rec.employee_last_name ||
3487 ' ; first NOAC/Second NOAC: '||
3488 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3489 l_ghr_pa_requests_rec.second_noa_code ||
3490 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3491
3492 Raise EHRI_DYNRPT_ERROR;
3493 END;
3494
3495 -- END IF; -- end of popluation of full record if not count_only
3496 --
3497
3498 --
3499 -- EHRI changes
3500 --
3501 IF ((l_ghr_pa_requests_rec.first_noa_code ='002'
3502 AND
3503 (NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '1%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '2%' or
3504 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '3%' or NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '4%' or
3505 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '5%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '6%' or
3506 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '7%' OR NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') like '8%' )
3507 ) -- for 002
3508 OR
3509 (l_ghr_pa_requests_rec.first_noa_code like '1%' or l_ghr_pa_requests_rec.first_noa_code like '2%' or
3510 l_ghr_pa_requests_rec.first_noa_code like '3%' or l_ghr_pa_requests_rec.first_noa_code like '4%' or
3511 l_ghr_pa_requests_rec.first_noa_code like '5%' OR l_ghr_pa_requests_rec.first_noa_code like '6%' or
3512 l_ghr_pa_requests_rec.first_noa_code like '7%' OR l_ghr_pa_requests_rec.first_noa_code like '8%'
3513 )
3514 )
3515 THEN
3516 l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3517 -- Bug 5010784
3518 -- l_ghr_cpdf_temp_rec.bargaining_unit_status := l_ghr_pa_requests_rec.bargaining_unit_status;
3519 l_ghr_cpdf_temp_rec.bargaining_unit_status := SUBSTR(l_ghr_pa_requests_rec.bargaining_unit_status,
3520 LENGTH(l_ghr_pa_requests_rec.bargaining_unit_status)-3);
3521 -- End Bug 5010784
3522
3523 -- adding code for 817 NOAC
3524 IF (l_ghr_pa_requests_rec.first_noa_code='817' or l_ghr_pa_requests_rec.second_noa_code='817') THEN
3525 l_ghr_cpdf_temp_rec := l_ghr_empty_cpdf_temp;
3526 l_ghr_cpdf_temp_rec.agency_code := NVL(l_ghr_pa_requests_rec.agency_code,l_ghr_pa_requests_rec.from_agency_code);
3527 l_ghr_cpdf_temp_rec.to_national_identifier
3528 := format_ni(l_ghr_pa_requests_rec.employee_national_identifier);
3529 l_ghr_cpdf_temp_rec.effective_date := l_ghr_pa_requests_rec.effective_date;
3530 l_ghr_cpdf_temp_rec.first_noa_code := l_ghr_pa_requests_rec.first_noa_code;
3531 l_ghr_cpdf_temp_rec.second_noa_code := l_ghr_pa_requests_rec.second_noa_code;
3532 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3533 l_ghr_cpdf_temp_rec.ehri_employee_id := l_ehri_id;
3534 -- l_ghr_cpdf_temp_rec.employee_date_of_birth := NULL;
3535 -- GOTO end_par_loop;
3536 END IF;
3537
3538 IF l_ghr_pa_requests_rec.first_noa_code <> '001' AND
3539 NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '3%' AND
3540 (l_ghr_pa_requests_rec.first_noa_code = '002' AND
3541 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '3%'
3542 )
3543 ) AND
3544 NOT (l_ghr_pa_requests_rec.first_noa_code LIKE '4%' AND
3545 (l_ghr_pa_requests_rec.first_noa_code = '002' OR
3546 NVL(l_ghr_pa_requests_rec.second_noa_code, '@#') LIKE '4%'
3547 )
3548 ) THEN
3549
3550 l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.to_position_title;
3551 l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3552
3553 /* FOR pos_org IN cur_pos_org(l_ghr_pa_requests_rec.to_organization_id,
3554 l_ghr_pa_requests_rec.effective_date)
3555 LOOP
3556 l_ghr_cpdf_temp_rec.POSITION_ORG := pos_org.name;
3557 END LOOP;
3558
3559 */
3560 l_ghr_cpdf_temp_rec.POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.to_position_org_line1 ||
3561 ' ' || l_ghr_pa_requests_rec.to_position_org_line2 ||
3562 ' ' || l_ghr_pa_requests_rec.to_position_org_line3 ||
3563 ' ' || l_ghr_pa_requests_rec.to_position_org_line4 ||
3564 ' ' || l_ghr_pa_requests_rec.to_position_org_line5 ||
3565 ' ' || l_ghr_pa_requests_rec.to_position_org_line6,1,500);
3566 ELSE
3567 l_ghr_cpdf_temp_rec.position_title := NULL;
3568 l_ghr_cpdf_temp_rec.position_number := NULL;
3569 l_ghr_cpdf_temp_rec.POSITION_ORG := NULL;
3570 --
3571 l_ghr_cpdf_temp_rec.position_title := NULL;
3572 l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3573 l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3574 l_ghr_cpdf_temp_rec.frozen_service := NULL;
3575 END IF;
3576
3577 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3578 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('815','816','817','818','819','825',
3579 '840','841','842','843','844','845','846','847','848','849','878','879')
3580 )
3581 OR
3582 l_ghr_pa_requests_rec.first_noa_code in ('815','816','817','818','819','825',
3583 '840','841','842','843','844','845','846','847','848','849','878','879')
3584 )
3585 THEN
3586
3587 IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='M' THEN
3588 l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.award_amount;
3589 END IF;
3590 IF NVL(l_ghr_pa_requests_rec.award_uom,'M')='H' THEN
3591 l_ghr_cpdf_temp_rec.award_hours := l_ghr_pa_requests_rec.award_amount;
3592 END IF;
3593 IF l_ghr_pa_requests_rec.award_percentage IS NOT NULL THEN
3594 l_ghr_cpdf_temp_rec.award_percentage := l_ghr_pa_requests_rec.award_percentage;
3595 END IF;
3596
3597 /* COMMENTED this code as the similar code is added at line 2545 to resolve the
3598 issue of non-printing the to total salary.
3599 -- Bug#5328177 Added NOA Codes 815,816 as they also belongs to the same category.
3600 -- Bug#3941541,5168358 Separation Incentive Changes.
3601 -- If the Award Dollars value is NOT NULL, Assume that 825 is processed as Award.
3602 -- Otherwise, it is processed as Incentive.
3603 IF (l_ghr_pa_requests_rec.first_noa_code IN ('815','816','825') OR
3604 l_ghr_pa_requests_rec.second_noa_code IN ('815','816', '825')) AND
3605 l_ghr_cpdf_temp_rec.award_dollars IS NULL THEN
3606 l_ghr_cpdf_temp_rec.award_dollars := l_ghr_pa_requests_rec.to_total_salary;
3607 l_ghr_cpdf_temp_rec.to_total_salary := NULL;
3608 END IF;
3609 -- End of Bug#3941541,5168358
3610 */
3611 -- DONT report if AWARD or 3xx,4xx actions
3612 l_ghr_cpdf_temp_rec.creditable_military_service := NULL;
3613 l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3614 l_ghr_cpdf_temp_rec.frozen_service := NULL;
3615
3616 END IF;
3617
3618 -- New EHRI changes MADHURI
3619 IF (
3620 NOT( (l_ghr_pa_requests_rec.first_noa_code='002' and
3621 l_ghr_pa_requests_rec.second_noa_code LIKE '1%')
3622 OR
3623 l_ghr_pa_requests_rec.first_noa_code LIKE '1%' )
3624 AND
3625 NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3626 l_ghr_pa_requests_rec.second_noa_code LIKE '2%')
3627 OR
3628 l_ghr_pa_requests_rec.first_noa_code LIKE '2%' )
3629 )
3630 THEN
3631
3632 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3633 l_ghr_pa_requests_rec.second_noa_code = '780')
3634 OR
3635 l_ghr_pa_requests_rec.first_noa_code='780'
3636 ) THEN
3637 --
3638 -- Prior Names ONLY FOR NAME CHANGE
3639
3640
3641 FOR prior_per_rec IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3642 (l_ghr_pa_requests_rec.effective_date-1) )
3643 LOOP
3644 -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3645 get_suffix_lname(prior_per_rec.last_name,
3646 l_ghr_pa_requests_rec.effective_date-1,
3647 l_suffix,
3648 l_last_name);
3649 l_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME := l_last_name;
3650 l_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME := prior_per_rec.first_name;
3651 l_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME := prior_per_rec.middle_names;
3652 l_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX := l_suffix;
3653 --End Bug# 4648811
3654 END LOOP;
3655
3656 END IF;
3657
3658 IF NOT ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3659 l_ghr_pa_requests_rec.second_noa_code = '817')
3660 OR
3661 l_ghr_pa_requests_rec.first_noa_code='817'
3662 ) THEN
3663 -- Dont report these items for 817 action
3664 --
3665 l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := l_ghr_pa_requests_rec.from_position_title;
3666 l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := l_ghr_pa_requests_rec.from_position_number;
3667 --
3668 /* FOR prior_pos_org IN cur_prior_pos_org(l_ghr_pa_requests_rec.from_position_id,
3669 (l_ghr_pa_requests_rec.effective_date-1) )
3670 LOOP
3671 l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := prior_pos_org.name;
3672 END LOOP; */
3673 l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := SUBSTR(l_ghr_pa_requests_rec.from_position_org_line1 ||
3674 ' ' || l_ghr_pa_requests_rec.from_position_org_line2 ||
3675 ' ' || l_ghr_pa_requests_rec.from_position_org_line3 ||
3676 ' ' || l_ghr_pa_requests_rec.from_position_org_line4 ||
3677 ' ' || l_ghr_pa_requests_rec.from_position_org_line5 ||
3678 ' ' || l_ghr_pa_requests_rec.from_position_org_line6,1,500);
3679
3680 ELSE
3681 l_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE := NULL;
3682 l_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER := NULL;
3683 l_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG := NULL;
3684 END IF;
3685 --
3686 END IF; --- NOT APPOINTMENT ACTION OR RETURN TO DUTY
3687
3688 l_ghr_cpdf_temp_rec.position_number := l_ghr_pa_requests_rec.to_position_number;
3689
3690 -- NEW EHRI CHANGES MADHURI
3691 --
3692 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' AND l_ghr_pa_requests_rec.second_noa_code <>'817')
3693 OR
3694 l_ghr_pa_requests_rec.first_noa_code NOT IN ('817')) THEN
3695 l_ghr_cpdf_temp_rec.employee_last_name := format_name_ehri(l_ghr_pa_requests_rec.employee_last_name);
3696 l_ghr_cpdf_temp_rec.employee_first_name := format_name_ehri(l_ghr_pa_requests_rec.employee_first_name);
3697 l_ghr_cpdf_temp_rec.employee_middle_names := format_name_ehri(l_ghr_pa_requests_rec.employee_middle_names);
3698 -- Added format_name_ehri for EHRI changes.
3699 FOR per_det IN cur_per_details(l_ghr_pa_requests_rec.person_id,
3700 l_ghr_pa_requests_rec.effective_date)
3701 LOOP
3702 -- Bug# 4648811 extracting suffix from the lastname and removing suffix from the lastname
3703 get_suffix_lname(per_det.last_name,
3704 l_ghr_pa_requests_rec.effective_date,
3705 l_suffix,
3706 l_last_name);
3707 l_ghr_cpdf_temp_rec.name_title := l_suffix;
3708 -- End Bug# 4648811
3709 END LOOP;
3710
3711
3712
3713 FOR scd_dates IN cur_scd_dates(l_ghr_pa_requests_rec.pa_request_id)
3714 LOOP
3715 l_ghr_cpdf_temp_rec.SCD_rif := fnd_date.canonical_to_date(scd_dates.rif);
3716 l_ghr_cpdf_temp_rec.SCD_retirement := fnd_date.canonical_to_date(scd_dates.ret);
3717 -- Added date conversion for bug#3808473-EHRI reports
3718 END LOOP;
3719
3720 IF (l_ghr_cpdf_temp_rec.SCD_rif IS NULL
3721 and l_ghr_cpdf_temp_rec.SCD_retirement IS NULL) THEN
3722
3723 BEGIN
3724 ghr_history_fetch.fetch_peopleei
3725 (p_person_id => l_ghr_pa_requests_rec.person_id,
3726 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
3727 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3728 p_per_ei_data => ll_per_ei_data
3729 );
3730
3731 l_ghr_cpdf_temp_rec.SCD_rif:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information5);
3732 l_ghr_cpdf_temp_rec.SCD_retirement:= fnd_date.canonical_to_date(ll_per_ei_data.pei_information7);
3733
3734 EXCEPTION
3735 WHEN OTHERS THEN
3736 l_message_name := 'fetch_peopleei';
3737 l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3738 l_ghr_pa_requests_rec.pa_request_id ||
3739 ' ; SSN/employee last name' ||
3740 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3741 l_ghr_pa_Requests_rec.employee_last_name ||
3742 ' ; first NOAC/Second NOAC: '||
3743 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3744 l_ghr_pa_requests_rec.second_noa_code ||
3745 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3746
3747 Raise EHRI_DYNRPT_ERROR;
3748 END;
3749 END IF;
3750
3751 ELSE -- if NOAC is 817
3752 l_ghr_cpdf_temp_rec.employee_last_name := NULL;
3753 l_ghr_cpdf_temp_rec.employee_first_name := NULL;
3754 l_ghr_cpdf_temp_rec.employee_middle_names := NULL;
3755 l_ghr_cpdf_temp_rec.name_title := NULL;
3756 l_ghr_cpdf_temp_rec.SCD_rif := NULL;
3757 l_ghr_cpdf_temp_rec.SCD_retirement := NULL;
3758 END IF; -- not in 817
3759 END IF;
3760 -- EHRI changes END
3761 --
3762
3763 --Pradeep start of Bug 3953500
3764 /* In case of 825 instead of taking TO side values From side values are taken as
3765 to side values are not populated.
3766 in future in case to side values are populated consider only the toside values.
3767 */
3768 IF l_ghr_pa_requests_rec.first_noa_code = '825'
3769 OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3770 l_ghr_pa_requests_rec.second_noa_code = '825' ) THEN
3771 l_ghr_cpdf_temp_rec.to_pay_plan := l_ghr_pa_requests_rec.from_pay_plan;
3772 l_ghr_cpdf_temp_rec.to_occ_code := l_ghr_pa_requests_rec.from_occ_code;
3773 l_ghr_cpdf_temp_rec.to_grade_or_level := l_ghr_pa_requests_rec.from_grade_or_level;
3774 l_ghr_cpdf_temp_rec.to_step_or_rate := l_ghr_pa_requests_rec.from_step_or_rate;
3775 l_ghr_cpdf_temp_rec.to_basic_pay := l_ghr_pa_requests_rec.from_basic_pay; -- format in report
3776 l_ghr_cpdf_temp_rec.to_pay_basis := l_ghr_pa_requests_rec.from_pay_basis;
3777 --
3778 l_ghr_pa_requests_rec.to_locality_adj := l_ghr_pa_requests_rec.from_locality_adj;
3779 l_ghr_pa_requests_rec.to_total_salary := l_ghr_pa_requests_rec.from_total_salary;
3780 l_ghr_pa_requests_rec.to_adj_basic_pay := l_ghr_pa_requests_rec.from_adj_basic_pay;
3781
3782 l_ghr_cpdf_temp_rec.to_pay_rate_determinant:= l_ghr_pa_requests_rec.pay_rate_determinant;
3783 l_ghr_cpdf_temp_rec.position_title := l_ghr_pa_requests_rec.from_position_title;
3784 END IF;
3785
3786 IF NOT (l_ghr_pa_requests_rec.first_noa_code like '3%'
3787 OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3788 l_ghr_pa_requests_rec.first_noa_code like '3%' ) )
3789 AND
3790 NOT (l_ghr_pa_requests_rec.first_noa_code like '4%'
3791 OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3792 l_ghr_pa_requests_rec.first_noa_code like '4%' ) )
3793 AND
3794 NOT (l_ghr_pa_requests_rec.first_noa_code = '817'
3795 OR ( l_ghr_pa_requests_rec.first_noa_code = '002' and
3796 l_ghr_pa_requests_rec.first_noa_code = '817' ) )
3797 THEN
3798 IF l_ghr_cpdf_temp_rec.to_supervisory_differential IS NULL THEN
3799
3800 BEGIN
3801 ghr_api.retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3802 ,p_input_value_name => 'Amount'
3803 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3804 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3805 ,p_value => l_ghr_cpdf_temp_rec.to_supervisory_differential
3806 ,p_multiple_error_flag => l_multi_error_flag);
3807 EXCEPTION
3808 WHEN OTHERS THEN
3809 l_message_name := 'retrieve_element_entry_value';
3810 l_log_text := 'Error in fetching Supervisory Differential Amount for pa_request_id: '||
3811 l_ghr_pa_requests_rec.pa_request_id ||
3812 ' ; SSN/employee last name' ||
3813 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3814 l_ghr_pa_Requests_rec.employee_last_name ||
3815 ' ; first NOAC/Second NOAC: '||
3816 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3817 l_ghr_pa_requests_rec.second_noa_code ||
3818 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3819 Raise EHRI_DYNRPT_ERROR;
3820 END;
3821
3822 END IF;
3823 IF l_ghr_cpdf_temp_rec.to_retention_allowance IS NULL THEN
3824
3825 BEGIN
3826 ghr_api.retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3827 ,p_input_value_name => 'Amount'
3828 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3829 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3830 ,p_value => l_ghr_cpdf_temp_rec.to_retention_allowance
3831 ,p_multiple_error_flag => l_multi_error_flag);
3832 EXCEPTION
3833 WHEN OTHERS THEN
3834 l_message_name := 'retrieve_element_entry_value';
3835 l_log_text := 'Error in fetching Retention Allowance Amount for pa_request_id: '||
3836 l_ghr_pa_requests_rec.pa_request_id ||
3837 ' ; SSN/employee last name' ||
3838 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3839 l_ghr_pa_Requests_rec.employee_last_name ||
3840 ' ; first NOAC/Second NOAC: '||
3841 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3842 l_ghr_pa_requests_rec.second_noa_code ||
3843 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3844 Raise EHRI_DYNRPT_ERROR;
3845 END;
3846
3847 END IF;
3848 END IF;
3849 --Pradeep end of Bug 3953500
3850
3851 -- 3327389 Bug fix start
3852 -- CPDF Reporting changes to include Creditable Military Service, Frozen Service and Prev Retirement Coverage
3853 -- including the NOACS 800 and 782 inspite they are optional for reporting
3854 -- as they will be anyways filtered under exclude_noacs
3855 BEGIN
3856
3857 IF ( (l_ghr_pa_requests_rec.first_noa_code='002' and
3858 NVL(l_ghr_pa_requests_rec.second_noa_code,'@#') in ('702','703','713','721','781','782','790','800',
3859 '850','855','867','868','891','892','893','895','899'))
3860 OR l_ghr_pa_requests_rec.first_noa_code in ('702','703','713','721','781','782','790','800',
3861 '850','855','867','868','891','892','893','895','899')
3862 ) THEN
3863
3864 ghr_history_fetch.fetch_peopleei
3865 (p_person_id => l_ghr_pa_requests_rec.person_id,
3866 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
3867 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3868 p_per_ei_data => ll_per_ei_data
3869 );
3870
3871 l_ghr_cpdf_temp_rec.creditable_military_service:= ll_per_ei_data.pei_information5;
3872 ll_per_ei_data :=NULL;
3873
3874 ghr_history_fetch.fetch_peopleei
3875 (p_person_id => l_ghr_pa_requests_rec.person_id,
3876 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
3877 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
3878 p_per_ei_data => ll_per_ei_data
3879 );
3880 l_ghr_cpdf_temp_rec.from_retirement_coverage := ll_per_ei_data.pei_information4;
3881 l_ghr_cpdf_temp_rec.Frozen_service:= ll_per_ei_data.pei_information5;
3882
3883 ll_per_ei_data:=NULL;
3884 END IF;
3885
3886 -- Bug 4714292 EHRI Reports Changes for EOY 05
3887 IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('5','6','E','F') THEN
3888 l_ghr_cpdf_temp_rec.from_spl_rate_supplement := l_ghr_cpdf_temp_rec.from_locality_adj;
3889 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3890 END IF;
3891
3892 IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('5','6','E','F') THEN
3893 l_ghr_cpdf_temp_rec.to_spl_rate_supplement := l_ghr_cpdf_temp_rec.to_locality_adj;
3894 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3895 END IF;
3896
3897 -- End Bug 4714292 EHRI Reports Changes for EOY 05
3898
3899 -- If Ethnicity is reported, RNO should be null
3900 IF l_ghr_cpdf_temp_rec.race_ethnic_info IS NOT NULL THEN
3901 l_ghr_cpdf_temp_rec.race_national_origin := NULL;
3902 END IF;
3903
3904 -- Bug 5011003
3905 l_locality_pay_area_code := get_loc_pay_area_code(p_duty_station_id => l_ghr_pa_requests_rec.duty_station_id,
3906 p_effective_date => l_ghr_pa_requests_rec.effective_date);
3907 l_equiv_plan := get_equivalent_pay_plan(NVL(l_retained_pay_plan, l_ghr_pa_requests_rec.to_pay_plan));
3908
3912
3909 IF l_ghr_cpdf_temp_rec.to_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
3910 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3911 ELSE
3913 IF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
3914 AND l_equiv_plan = 'GS' THEN
3915 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3916 ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND l_equiv_plan = 'GS'
3917 AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
3918 l_ghr_cpdf_temp_rec.to_locality_adj := 0;
3919 ELSIF l_ghr_cpdf_temp_rec.to_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
3920 l_ghr_cpdf_temp_rec.to_locality_adj := NULL;
3921 END IF;
3922
3923 END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
3924
3925 -- For Prior locality pay
3926 IF l_ghr_cpdf_temp_rec.from_pay_rate_determinant IN ('3', 'J', 'K', 'U', 'V', '6', 'E', 'F') THEN
3927 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3928 ELSE
3929
3930 IF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_locality_pay_area_code = 'ZZ'
3931 AND l_equiv_plan = 'GS' THEN
3932 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3933 ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND l_equiv_plan = 'GS'
3934 AND NVL(l_locality_pay_area_code,'-1') <> 'ZZ' THEN
3935 l_ghr_cpdf_temp_rec.from_locality_adj := 0;
3936 ELSIF l_ghr_cpdf_temp_rec.from_locality_adj = 0 AND NVL(l_equiv_plan,'-1') <> 'GS' THEN
3937 l_ghr_cpdf_temp_rec.from_locality_adj := NULL;
3938 END IF;
3939
3940 END IF; -- IF PRD IN ('3', 'J', 'K', 'U',
3941
3942 -- End Bug 5011003
3943
3944 EXCEPTION
3945 WHEN OTHERS THEN
3946 l_message_name := 'fetch_peopleei';
3947 l_log_text := 'Error in fetching SCD Information for pa_request_id: '||
3948 l_ghr_pa_requests_rec.pa_request_id ||
3949 ' ; SSN/employee last name' ||
3950 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
3951 l_ghr_pa_Requests_rec.employee_last_name ||
3952 ' ; first NOAC/Second NOAC: '||
3953 l_ghr_pa_requests_rec.first_noa_code || ' / '||
3954 l_ghr_pa_requests_rec.second_noa_code ||
3955 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3956 END;
3957 -- End of changes for CPDF reports picking creditable mil serv, frozen serv and prev retirement coverage
3958 -- 3327389 Bug fix end
3959 IF l_ghr_pa_requests_rec.first_noa_code <> '001' THEN
3960 IF l_ghr_cpdf_temp_rec.from_retirement_coverage IS NULL THEN
3961 l_ghr_cpdf_temp_rec.from_retirement_coverage:= 'NA';
3962 ELSIF l_ghr_cpdf_temp_rec.from_retirement_coverage IN ('P','R') THEN --bug#5184157 yogi
3963 l_ghr_cpdf_temp_rec.from_retirement_coverage:= 'Y';
3964 END IF;
3965 ELSE
3966 l_ghr_cpdf_temp_rec.from_retirement_coverage := NULL;
3967 END If;
3968
3969
3970 --Bug #6158983 EHRI Status and Dynamic Report Changes
3971 BEGIN
3972
3973 FOR bus_grp_rec in cur_per_details(l_ghr_pa_requests_rec.person_id,
3974 l_ghr_pa_requests_rec.effective_date)
3975 LOOP
3976 l_business_group_id := bus_grp_rec.business_group_id;
3977 END LOOP;
3978
3979 l_value := null;
3980 l_effective_start_date := null;
3981
3982 l_message_name := 'Fetch HB Pre Tax';
3983 ghr_per_sum.get_element_details
3984 (p_element_name => 'Health Benefits Pre tax'
3985 ,p_input_value_name => 'Health Plan'
3986 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
3987 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
3988 ,p_value => l_value
3989 ,p_effective_start_date => l_effective_start_date
3990 ,p_business_group_id => l_business_group_id);
3991
3992 l_ghr_cpdf_temp_rec.health_plan := l_value;
3993 l_ghr_cpdf_temp_rec.fehb_elect_eff_date := l_effective_start_date;
3994 -- Reporting Plan + Enrollment as Health Plan.
3995 l_message_name := 'Fetch HB Pre Tax Enroll';
3996
3997 l_value := null;
3998 l_effective_start_date := null;
3999 ghr_per_sum.get_element_details
4000 (p_element_name => 'Health Benefits Pre tax'
4001 ,p_input_value_name => 'Enrollment'
4002 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4003 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4004 ,p_value => l_value
4005 ,p_effective_start_date => l_effective_start_date
4006 ,p_business_group_id => l_business_group_id);
4007
4008
4009 l_ghr_cpdf_temp_rec.health_plan := l_ghr_cpdf_temp_rec.health_plan||l_value;
4010
4011 if l_ghr_cpdf_temp_rec.health_plan is Null then
4012
4013 l_message_name := 'Fetch HB plan';
4014 ghr_per_sum.get_element_details
4015 (p_element_name => 'Health Benefits'
4016 ,p_input_value_name => 'Health Plan'
4017 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4021 ,p_business_group_id => l_business_group_id);
4018 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4019 ,p_value => l_value
4020 ,p_effective_start_date => l_effective_start_date
4022
4023 l_ghr_cpdf_temp_rec.health_plan := l_value;
4024 l_ghr_cpdf_temp_rec.fehb_elect_eff_date := l_effective_start_date;
4025 -- Reporting Plan + Enrollment as Health Plan.
4026 l_message_name := 'Fetch HB Enrollment';
4027 l_value := null;
4028 l_effective_start_date := null;
4029 ghr_per_sum.get_element_details
4030 (p_element_name => 'Health Benefits'
4031 ,p_input_value_name => 'Enrollment'
4032 ,p_assignment_id => l_ghr_pa_requests_rec.employee_assignment_id
4033 ,p_effective_date => l_ghr_pa_requests_rec.effective_date
4034 ,p_value => l_value
4035 ,p_effective_start_date => l_effective_start_date
4036 ,p_business_group_id => l_business_group_id);
4037
4038 l_ghr_cpdf_temp_rec.health_plan := l_ghr_cpdf_temp_rec.health_plan||l_value;
4039 end if;
4040
4041 EXCEPTION
4042 WHEN OTHERS THEN
4043 l_log_text := 'Error in fetching data for Health Benefits for pa_request_id: '||
4044 l_ghr_pa_requests_rec.pa_request_id ||
4045 ' ; SSN/employee last name' ||
4046 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4047 l_ghr_pa_Requests_rec.employee_last_name ||
4048 ' ; first NOAC/Second NOAC: '||
4049 l_ghr_pa_requests_rec.first_noa_code || ' / '||
4050 l_ghr_pa_requests_rec.second_noa_code ||
4051 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4052 Raise EHRI_DYNRPT_ERROR;
4053 END;
4054
4055 --- Bug 6312144 commented the below code as the below information will be fetched from RPA -- EIT's
4056
4057
4058 /*BEGIN
4059 ll_per_ei_data:=NULL;
4060 l_message_name := 'Fetch Retirement System Info';
4061 ghr_history_fetch.fetch_peopleei
4062 (p_person_id => l_ghr_pa_requests_rec.person_id,
4063 p_information_type => 'GHR_US_PER_RETIRMENT_SYS_INFO',
4064 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4065 p_per_ei_data => ll_per_ei_data
4066 );
4067
4068
4069 l_ghr_cpdf_temp_rec.SPECIAL_POPULATION_CODE := ll_per_ei_data.pei_information1;
4070 l_ghr_cpdf_temp_rec.CSRS_EXC_APPTS := ll_per_ei_data.pei_information2;
4071 l_ghr_cpdf_temp_rec.FERS_EXC_APPTS := ll_per_ei_data.pei_information3;
4072 l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND1 := ll_per_ei_data.pei_information4;
4073 l_ghr_cpdf_temp_rec.FICA_COVERAGE_IND2 := ll_per_ei_data.pei_information5;
4074 EXCEPTION
4075
4076 WHEN OTHERS THEN
4077
4078 l_log_text := 'Error in fetching data for Retirement System Information: '||
4079 l_ghr_pa_requests_rec.pa_request_id ||
4080 ' ; SSN/employee last name' ||
4081 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4082 l_ghr_pa_Requests_rec.employee_last_name ||
4083 ' ; first NOAC/Second NOAC: '||
4084 l_ghr_pa_requests_rec.first_noa_code || ' / '||
4085 l_ghr_pa_requests_rec.second_noa_code ||
4086 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4087 Raise EHRI_DYNRPT_ERROR;
4088 END;
4089
4090 BEGIN
4091 ll_per_ei_data:=NULL;
4092 ghr_history_fetch.fetch_peopleei
4093 (p_person_id => l_ghr_pa_requests_rec.person_id,
4094 p_information_type => 'GHR_US_PER_BENEFIT_INFO',
4095 p_date_effective => nvl(l_ghr_pa_requests_rec.effective_date,trunc(sysdate)),
4096 p_per_ei_data => ll_per_ei_data
4097 );
4098
4099 l_ghr_cpdf_temp_rec.FEGLI_ASSG_INDICATOR := ll_per_ei_data.pei_information16;
4100 l_ghr_cpdf_temp_rec.FEGLI_POST_ELC_BASIC_INS_AMT:= ll_per_ei_data.pei_information17;
4101 l_ghr_cpdf_temp_rec.FEGLI_COURT_ORD_IND := ll_per_ei_data.pei_information18;
4102 l_ghr_cpdf_temp_rec.FEGLI_BENF_DESG_IND := ll_per_ei_data.pei_information19;
4103 l_ghr_cpdf_temp_rec.FEHB_EVENT_CODE := ll_per_ei_data.pei_information20;
4104
4105 EXCEPTION
4106
4107 WHEN OTHERS THEN
4108
4109 l_log_text := 'Error in fetching data for Person Benefit Information: '||
4110 l_ghr_pa_requests_rec.pa_request_id ||
4111 ' ; SSN/employee last name' ||
4112 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4113 l_ghr_pa_Requests_rec.employee_last_name ||
4114 ' ; first NOAC/Second NOAC: '||
4115 l_ghr_pa_requests_rec.first_noa_code || ' / '||
4116 l_ghr_pa_requests_rec.second_noa_code ||
4117 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4118 Raise EHRI_DYNRPT_ERROR;
4119 END;*/
4120 --bug #6416590 moving sf50_approval_date to pareq_last_updated_date
4121 -- as sf50_approval_date has to be considered for ordering of sequence number
4122 --with in effective date
4123 l_ghr_cpdf_temp_rec.pareq_last_updated_date := l_ghr_pa_requests_rec.sf50_approval_date;
4124 --End of Bug#6158983
4125
4126
4127 insert_row(l_ghr_cpdf_temp_rec);
4128 l_records_found:=TRUE;
4129 --
4130 <<end_par_loop>>
4131 NULL;
4132
4133 EXCEPTION
4134 WHEN EHRI_DYNRPT_ERROR THEN
4135 hr_utility.set_location('Inside EHRI_DYNRPT_ERROR exception ',30);
4136 ghr_mto_int.log_message(p_procedure => l_message_name,
4137 p_message => l_log_text
4138 );
4139 COMMIT;
4140 WHEN OTHERS THEN
4141 hr_utility.set_location('Inside WHEN_OTHERS exception ',40);
4142 l_message_name := 'Unhandled Error';
4143 l_log_text := 'Unhandled Error for pa_request_id: '||
4144 l_ghr_pa_requests_rec.pa_request_id ||
4145 ' ; SSN/employee last name' ||
4146 l_ghr_pa_requests_rec.employee_national_identifier ||' / '||
4147 l_ghr_pa_Requests_rec.employee_last_name ||
4148 ' ; first NOAC/Second NOAC: '||
4149 l_ghr_pa_requests_rec.first_noa_code || ' / '||
4150 l_ghr_pa_requests_rec.second_noa_code ||
4151 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4152 ghr_mto_int.log_message(p_procedure => l_message_name,
4153 p_message => l_log_text
4154 );
4155 COMMIT;
4156
4157 END;
4158 END LOOP; -- end of check for single flag or dual flag
4159 END LOOP;
4160
4161 IF NOT l_records_found THEN
4162 l_message_name:='RECORDS_NOT_FOUND';
4163 l_log_text:= 'No Records found for the period '||p_start_Date||' - '||p_end_date;
4164 ghr_mto_int.log_message(p_procedure => l_message_name,
4165 p_message => l_log_text
4166 );
4167
4168 l_mesgbuff1:='No Records found for the period '||p_start_Date||' - '||p_end_date;
4169 fnd_file.put(fnd_file.log,l_mesgbuff1);
4170 fnd_file.new_line(fnd_file.log);
4171 END IF;
4172
4173 ELSE -- DATES are not proper
4174 l_message_name:= 'CHECK_REPORT_FROM_TO_DATES';
4175 l_log_text:='The Report To Date: '||p_end_date||' is less than the Report From Date: '||p_start_date||
4176 '. Please enter a value for Report To Date greater than or equal to the Report Start Date';
4177
4178 ghr_mto_int.log_message(p_procedure => l_message_name,
4179 p_message => l_log_text
4180 );
4181 END IF;
4182
4183 --
4184 hr_utility.set_location('Leaving:'||l_proc, 99);
4185 -- hr_utility.trace_off;
4186 --
4187 END populate_ghr_cpdf_temp;
4188
4189
4190 ---------------------------------------------------------------------------------------------
4191 -- This is the Main procedure called from the concurrent program
4192 ---------------------------------------------------------------------------------------------
4193
4194
4195 PROCEDURE ehri_dynamics_main
4196 ( errbuf OUT NOCOPY VARCHAR2
4197 ,retcode OUT NOCOPY NUMBER
4198 ,p_report_name IN VARCHAR2
4199 ,p_agency_code IN VARCHAR2
4200 ,p_agency_subelement IN VARCHAR2
4201 ,p_start_date IN VARCHAR2
4202 ,p_end_date IN VARCHAR2
4203 ,p_gen_xml_file IN VARCHAR2 DEFAULT 'N'
4204 ,p_gen_txt_file IN VARCHAR2 DEFAULT 'Y'
4205 )
4206 IS
4207 l_ascii_fname varchar2(80);
4208 l_xml_fname varchar2(80);
4209 l_count_only BOOLEAN;
4210 l_file_name VARCHAR2(500);
4211 l_start_date DATE;
4212 l_end_date DATE;
4213 l_ret_code NUMBER;
4214 l_invalid_filename EXCEPTION;
4215 l_report_name VARCHAR2(500);
4216 l_log_text ghr_process_log.log_text%type;
4217 l_message_name ghr_process_log.message_name%type;
4218
4219 --
4220 BEGIN
4221 l_report_name := p_report_name;
4222 -- Need to convert the dates from canonical to Date
4223 l_start_date := fnd_date.canonical_to_date(p_start_date);
4224 l_end_date := fnd_date.canonical_to_date(p_end_date);
4225 l_ret_code := 0;
4226 --
4227 populate_ghr_cpdf_temp(p_agency_code||NVL(p_agency_subelement,'%'),l_start_date,l_end_date,FALSE);
4228 -- Generate ASCII and XML files
4229 WritetoFile(l_report_name,p_gen_xml_file,p_gen_txt_file);
4230
4231 -- Purge the table contents after reporting
4232 cleanup_table;
4233 EXCEPTION
4234 WHEN OTHERS THEN
4235 l_message_name := 'Unhandled Error';
4236 l_log_text := 'Unhandled Error under procedure ehri_dynamics_main'||
4237 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
4238 ghr_mto_int.log_message(p_procedure => l_message_name,
4239 p_message => l_log_text
4240 );
4241 COMMIT;
4242 END ehri_dynamics_main;
4243
4244 --
4245 END ghr_ehri_dynrpt;