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