[Home] [Help]
PACKAGE BODY: APPS.GHR_CPDF_EHRIS
Source
1 PACKAGE BODY ghr_cpdf_ehris AS
2 /* $Header: ghrehris.pkb 120.52.12020000.6 2012/12/17 06:15:53 utokachi ship $ */
3
4 g_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE;
5 g_pay_table_name varchar2(30);
6 g_to_pay_plan varchar2(30);
7 g_retained_pay_table_name varchar2(30);
8
9 PROCEDURE initialize_record
10 IS
11 l_proc varchar2(30) := 'initialize_record';
12 BEGIN
13 hr_utility.set_location('Entering:'||l_proc,5);
14 g_ghr_cpdf_temp.academic_discipline := NULL;
15 g_ghr_cpdf_temp.agency_code := NULL;
16 g_ghr_cpdf_temp.annuitant_indicator := NULL;
17 g_ghr_cpdf_temp.award_amount := NULL;
18 g_ghr_cpdf_temp.bargaining_unit_status := NULL;
19 g_ghr_cpdf_temp.benefit_amount := NULL;
20 g_ghr_cpdf_temp.citizenship := NULL;
21 g_ghr_cpdf_temp.creditable_military_service := NULL;
22 g_ghr_cpdf_temp.current_appointment_auth1 := NULL;
23 g_ghr_cpdf_temp.current_appointment_auth2 := NULL;
24 g_ghr_cpdf_temp.to_duty_station_code := NULL;
25 g_ghr_cpdf_temp.education_level := NULL;
26 g_ghr_cpdf_temp.effective_date := NULL;
27 g_ghr_cpdf_temp.employee_date_of_birth := NULL;
28 g_ghr_cpdf_temp.employee_first_name := NULL;
29 g_ghr_cpdf_temp.employee_last_name := NULL;
30 g_ghr_cpdf_temp.employee_middle_names := NULL;
31 g_ghr_cpdf_temp.from_national_identifier := NULL;
32 g_ghr_cpdf_temp.fegli := NULL;
33 g_ghr_cpdf_temp.fers_coverage := NULL;
34 g_ghr_cpdf_temp.first_action_la_code1 := NULL;
35 g_ghr_cpdf_temp.first_action_la_code2 := NULL;
36 g_ghr_cpdf_temp.first_noa_code := NULL;
37 g_ghr_cpdf_temp.flsa_category := NULL;
38 g_ghr_cpdf_temp.from_basic_pay := NULL;
39 g_ghr_cpdf_temp.from_duty_station_code := NULL;
40 g_ghr_cpdf_temp.from_grade_or_level := NULL;
41 g_ghr_cpdf_temp.from_locality_adj := NULL;
42 g_ghr_cpdf_temp.from_occ_code := NULL;
43 g_ghr_cpdf_temp.from_pay_table_id := NULL;
44 g_ghr_cpdf_temp.from_pay_basis := NULL;
45 g_ghr_cpdf_temp.from_pay_plan := NULL;
46 g_ghr_cpdf_temp.from_pay_rate_determinant := NULL;
47 g_ghr_cpdf_temp.from_retirement_coverage := NULL;
48 g_ghr_cpdf_temp.from_step_or_rate := NULL;
49 g_ghr_cpdf_temp.from_total_salary := NULL;
50 g_ghr_cpdf_temp.from_work_schedule := NULL;
51 g_ghr_cpdf_temp.frozen_service := NULL;
52 g_ghr_cpdf_temp.functional_class := NULL;
53 g_ghr_cpdf_temp.handicap_code := NULL;
54 g_ghr_cpdf_temp.health_plan := NULL;
55 g_ghr_cpdf_temp.individual_group_award := NULL;
56 g_ghr_cpdf_temp.organizational_component := NULL;
57 g_ghr_cpdf_temp.pay_status := NULL;
58 g_ghr_cpdf_temp.personnel_office_id := NULL;
59 g_ghr_cpdf_temp.position_occupied := NULL;
60 g_ghr_cpdf_temp.race_national_origin := NULL;
61 g_ghr_cpdf_temp.rating_of_record := NULL;
62 g_ghr_cpdf_temp.rating_of_record_level := NULL;
63 g_ghr_cpdf_temp.rating_of_record_pattern := NULL;
64 g_ghr_cpdf_temp.rating_of_record_period_starts := NULL;
65 g_ghr_cpdf_temp.rating_of_record_period_ends := NULL;
66 g_ghr_cpdf_temp.retained_grade_or_level := NULL;
67 g_ghr_cpdf_temp.retained_pay_plan := NULL;
68 g_ghr_cpdf_temp.retained_step_or_rate := NULL;
69 g_ghr_cpdf_temp.retirement_plan := NULL;
70 g_ghr_cpdf_temp.second_noa_code := NULL;
71 g_ghr_cpdf_temp.service_comp_date := NULL;
72 g_ghr_cpdf_temp.sex := NULL;
73 g_ghr_cpdf_temp.supervisory_status := NULL;
74 g_ghr_cpdf_temp.tenure := NULL;
75 g_ghr_cpdf_temp.to_basic_pay := NULL;
76 g_ghr_cpdf_temp.to_grade_or_level := NULL;
77 g_ghr_cpdf_temp.to_locality_adj := NULL;
78 g_ghr_cpdf_temp.to_national_identifier := NULL;
79 g_ghr_cpdf_temp.to_occ_code := NULL;
80 g_ghr_cpdf_temp.to_pay_basis := NULL;
81 g_ghr_cpdf_temp.to_pay_plan := NULL;
82 g_ghr_cpdf_temp.to_pay_rate_determinant := NULL;
83 g_ghr_cpdf_temp.to_pay_table_id := NULL;
84 g_ghr_cpdf_temp.to_retention_allowance := NULL;
85 g_ghr_cpdf_temp.to_staffing_differential := NULL;
86 g_ghr_cpdf_temp.to_step_or_rate := NULL;
87 g_ghr_cpdf_temp.to_supervisory_differential := NULL;
88 g_ghr_cpdf_temp.to_total_salary := NULL;
89 g_ghr_cpdf_temp.to_work_schedule := NULL;
90 g_ghr_cpdf_temp.veterans_preference := NULL;
91 g_ghr_cpdf_temp.veterans_status := NULL;
92 g_ghr_cpdf_temp.year_degree_attained := NULL;
93 g_duty_station_id := NULL;
94 g_ghr_cpdf_temp.SCD_retirement := NULL;
95 g_ghr_cpdf_temp.SCD_rif := NULL;
96 g_ghr_cpdf_temp.position_title := NULL;
97 g_ghr_cpdf_temp.name_title := NULL;
98
99 g_ghr_cpdf_temp.ehri_employee_id := NULL;
100 g_ghr_cpdf_temp.agency_employee_id := NULL;
101 g_ghr_cpdf_temp.world_citizenship := NULL;
102 g_ghr_cpdf_temp.slct_serv_regi_indicator := NULL;
103 g_ghr_cpdf_temp.svc_oblig_type_code1 := NULL;
104 g_ghr_cpdf_temp.svc_oblig_type_end_date1 := NULL;
105 g_ghr_cpdf_temp.svc_oblig_type_code2 := NULL;
106 g_ghr_cpdf_temp.svc_oblig_type_end_date2 := NULL;
107 g_ghr_cpdf_temp.svc_oblig_type_code3 := NULL;
108 g_ghr_cpdf_temp.svc_oblig_type_end_date3 := NULL;
109 g_ghr_cpdf_temp.svc_oblig_type_code4 := NULL;
110 g_ghr_cpdf_temp.svc_oblig_type_end_date4 := NULL;
111 g_ghr_cpdf_temp.appoint_type_code := NULL;
112 g_ghr_cpdf_temp.part_time_hours := NULL;
113 g_ghr_cpdf_temp.to_adj_basic_pay := NULL;
114 g_ghr_cpdf_temp.spcl_pay_tbl_type := NULL;
115 g_ghr_cpdf_temp.act_svc_indicator := NULL;
116 g_ghr_cpdf_temp.appropriation_code := NULL;
117 g_ghr_cpdf_temp.comp_pos_indicator := NULL;
118 g_ghr_cpdf_temp.mil_char_svc_code := NULL;
119 g_ghr_cpdf_temp.mil_svc_sno := NULL;
120 g_ghr_cpdf_temp.mil_svc_start_date := NULL;
121 g_ghr_cpdf_temp.mil_svc_end_date := NULL;
122 g_ghr_cpdf_temp.mil_branch_code := NULL;
123 g_ghr_cpdf_temp.mil_discharge_code := NULL;
124 g_ghr_cpdf_temp.career_tenure_code := NULL;
125 g_ghr_cpdf_temp.fegli_life_change_code := NULL;
126 g_ghr_cpdf_temp.fegli_life_event_date := NULL;
127 g_ghr_cpdf_temp.fegli_elect_date := NULL;
128 g_ghr_cpdf_temp.fehb_event_code := NULL;
129 g_ghr_cpdf_temp.tsp_eligibility_date := NULL;
130 g_ghr_cpdf_temp.tsp_effective_date := NULL;
131 g_ghr_cpdf_temp.tsp_elect_contrib_pct := NULL;
132 g_ghr_cpdf_temp.tsp_emp_amount := NULL;
133 g_ghr_cpdf_temp.fers_elect_date := NULL;
134 g_ghr_cpdf_temp.fers_elect_indicator := NULL;
135 g_ghr_cpdf_temp.alb_indicator := NULL;
136 g_ghr_cpdf_temp.alb_elect_date := NULL;
137 g_ghr_cpdf_temp.alb_notify_date := NULL;
138 g_ghr_cpdf_temp.fegli_indicator := NULL;
139 g_ghr_cpdf_temp.fegli_elect_date := NULL;
140 g_ghr_cpdf_temp.fegli_notify_date := NULL;
141 g_ghr_cpdf_temp.fehb_indicator := NULL;
142 --g_ghr_cpdf_temp.fehb_elect_date := NULL;
143 g_ghr_cpdf_temp.fehb_notify_date := NULL;
144
145 --bug#6158983
146 g_ghr_cpdf_temp.fehb_elect_eff_date := NULL;
147 g_ghr_cpdf_temp.appointment_nte_date := NULL;
148 --end of 6158983
149 g_ghr_cpdf_temp.retire_indicator := NULL;
150 g_ghr_cpdf_temp.retire_elect_date := NULL;
151 g_ghr_cpdf_temp.retire_notify_date := NULL;
152 g_ghr_cpdf_temp.cont_elect_date := NULL;
153 g_ghr_cpdf_temp.cont_notify_date := NULL;
154 g_ghr_cpdf_temp.cont_term_elect_date := NULL;
155 g_ghr_cpdf_temp.cont_ins_pay_notify_date := NULL;
156 g_ghr_cpdf_temp.cont_pay_type_code := NULL;
157 g_ghr_cpdf_temp.scd_ses := NULL;
158 g_ghr_cpdf_temp.scd_spcl_retire := NULL;
159 g_ghr_cpdf_temp.leave_scd := NULL;
160 g_ghr_cpdf_temp.tsp_scd := NULL;
161 g_ghr_cpdf_temp.disability_retire_notify := NULL;
162 g_ghr_cpdf_temp.work_address_line1 := NULL;
163 g_ghr_cpdf_temp.work_address_line2 := NULL;
164 g_ghr_cpdf_temp.work_address_line3 := NULL;
165 g_ghr_cpdf_temp.work_address_line4 := NULL;
166 g_ghr_cpdf_temp.work_city := NULL;
167 g_ghr_cpdf_temp.work_region := NULL; --Bug# 4725292
168 g_ghr_cpdf_temp.work_state_code := NULL;
169 g_ghr_cpdf_temp.work_postal_code := NULL;
170 g_ghr_cpdf_temp.work_country_code := NULL;
171 g_ghr_cpdf_temp.work_employee_email := NULL;
172 g_ghr_cpdf_temp.work_phone_number := NULL;
173 g_ghr_cpdf_temp.home_phone_number := NULL;
174 g_ghr_cpdf_temp.cell_phone_number := NULL;
175 g_ghr_cpdf_temp.emrgncy_cntct_family_name1 := NULL;
176 g_ghr_cpdf_temp.emrgncy_cntct_given_name1 := NULL;
177 g_ghr_cpdf_temp.emrgncy_cntct_middle_name1 := NULL;
178 g_ghr_cpdf_temp.emrgncy_cntct_suffix1 := NULL;
179 g_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt1 := NULL;
180 g_ghr_cpdf_temp.emrgncy_cntct_phone1 := NULL;
181 g_ghr_cpdf_temp.emrgncy_cntct_family_name2 := NULL;
182 g_ghr_cpdf_temp.emrgncy_cntct_given_name2 := NULL;
183 g_ghr_cpdf_temp.emrgncy_cntct_middle_name2 := NULL;
184 g_ghr_cpdf_temp.emrgncy_cntct_suffix2 := NULL;
185 g_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt2 := NULL;
186 g_ghr_cpdf_temp.emrgncy_cntct_phone2 := NULL;
187 g_ghr_cpdf_temp.language_code1 := NULL;
188 g_ghr_cpdf_temp.lang_prof_type1 := NULL;
189 g_ghr_cpdf_temp.lang_prof_level1 := NULL;
190 g_ghr_cpdf_temp.language_code2 := NULL;
191 g_ghr_cpdf_temp.lang_prof_type2 := NULL;
192 g_ghr_cpdf_temp.lang_prof_level2 := NULL;
193 g_ghr_cpdf_temp.language_code3 := NULL;
194 g_ghr_cpdf_temp.lang_prof_type3 := NULL;
195 g_ghr_cpdf_temp.lang_prof_level3 := NULL;
196 g_ghr_cpdf_temp.language_code4 := NULL;
197 g_ghr_cpdf_temp.lang_prof_type4 := NULL;
198 g_ghr_cpdf_temp.lang_prof_level4 := NULL;
199 g_ghr_cpdf_temp.language_code5 := NULL;
200 g_ghr_cpdf_temp.lang_prof_type5 := NULL;
201 g_ghr_cpdf_temp.lang_prof_level5 := NULL;
202 g_ghr_cpdf_temp.language_code6 := NULL;
203 g_ghr_cpdf_temp.lang_prof_type6 := NULL;
204 g_ghr_cpdf_temp.lang_prof_level6 := NULL;
205 g_ghr_cpdf_temp.language_code7 := NULL;
206 g_ghr_cpdf_temp.lang_prof_type7 := NULL;
207 g_ghr_cpdf_temp.lang_prof_level7 := NULL;
208 g_ghr_cpdf_temp.language_code8 := NULL;
209 g_ghr_cpdf_temp.lang_prof_type8 := NULL;
210 g_ghr_cpdf_temp.lang_prof_level8 := NULL;
211 g_ghr_cpdf_temp.spcl_salary_rate := NULL;
212 g_ghr_cpdf_temp.race_ethnic_info := NULL;
213 g_ghr_cpdf_temp.to_spl_rate_supplement := NULL;
214 g_ghr_cpdf_temp.Telework_Indicator := NULL;--bug# 14245991
215
216
217 END initialize_record;
218
219 PROCEDURE cleanup_table
220 IS
221 l_proc varchar2(30) := 'cleanup_table';
222 BEGIN
223 hr_utility.set_location('Entering:'||l_proc,5);
224 DELETE FROM ghr_cpdf_temp
225 WHERE report_type = 'STATUS'
226 AND session_id = userenv('SESSIONID');
227 END cleanup_table;
228
229 PROCEDURE get_appointment_date (p_person_id IN NUMBER
230 ,p_report_date IN DATE
231 ,p_appointment_date OUT NOCOPY DATE) IS
232
233 -- Cursor modified for Performance changes
234 /* CURSOR cur_per IS
235 SELECT per.hire_date
236 FROM per_people_v per
237 WHERE per.person_id = p_person_id; */
238 -- Bug 3742271, 3757124 - Added p_person_id in the following condition.
239 CURSOR cur_per IS
240 SELECT
241 DECODE(PER.CURRENT_EMPLOYEE_FLAG, 'Y', PPS.DATE_START, DECODE(PER.CURRENT_NPW_FLAG, 'Y', PPP.DATE_START, NULL)) hire_date
242 FROM
243 per_all_people PER ,
244 PER_PERIODS_OF_SERVICE PPS ,
245 PER_PERIODS_OF_PLACEMENT PPP
246 WHERE
247 PPS.PERSON_ID (+) = PER.PERSON_ID AND
248 PPP.PERSON_ID (+) = PER.PERSON_ID AND
249 PER.PERSON_ID = p_person_id AND
250 --Bug #9459539 Modified to query depending upon report date to get 1 record
251 P_REPORT_DATE BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND
252 (
253 (PER.EMPLOYEE_NUMBER IS NULL) OR
254 (PER.EMPLOYEE_NUMBER IS NOT NULL AND
255 PPS.DATE_START = (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID = PER.PERSON_ID AND
256 PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND
257 ((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL AND
258 PPP.DATE_START = (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND
259 PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)));
260
261 --
262 BEGIN
263
264 FOR cur_per_rec IN cur_per LOOP
265 p_appointment_date := cur_per_rec.hire_date;
266 END LOOP;
267
268 END get_appointment_date;
269 --
270 PROCEDURE get_from_history_asgnei
271 (
272 p_sr_assignment_id IN NUMBER
273 ,p_sr_report_date IN DATE
274 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
275 )
276 IS
277 l_proc varchar2(30) := 'get_from_history_asgnei';
278 l_ASGNEI_DATA PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
279 l_ASGNEI_DATA_INIT PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
280 l_session ghr_history_api.g_session_var_type;
281 l_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type;
282 l_result varchar2(20);
283 l_non_asg_ei_data PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
284
285 BEGIN
286 hr_utility.set_location('Entering:'||l_proc,5);
287
288 l_ASGNEI_DATA := l_ASGNEI_DATA_INIT;
289 -- Begin Bug# 4753092
290 g_message_name := 'Assignment EIT: Assigment RPA';
291 -- End Bug# 4753092
292 GHR_HISTORY_FETCH.fetch_asgei(
293 p_assignment_id => p_sr_assignment_id,
294 p_information_type => 'GHR_US_ASG_SF52',
295 p_date_effective => p_sr_report_date,
296 p_asg_ei_data => l_ASGNEI_DATA
297 );
298 p_sr_ghr_cpdf_temp.annuitant_indicator := l_ASGNEI_DATA.AEI_INFORMATION5;
299 p_sr_ghr_cpdf_temp.to_step_or_rate := l_ASGNEI_DATA.AEI_INFORMATION3;
300 p_sr_ghr_cpdf_temp.to_pay_rate_determinant := l_ASGNEI_DATA.AEI_INFORMATION6;
301 p_sr_ghr_cpdf_temp.tenure := l_ASGNEI_DATA.AEI_INFORMATION4;
302 p_sr_ghr_cpdf_temp.to_work_schedule := l_ASGNEI_DATA.AEI_INFORMATION7;
303 p_sr_ghr_cpdf_temp.part_time_hours := l_ASGNEI_DATA.AEI_INFORMATION8;
304
305 -- FWFA Changes Retrieved pay table id
306 p_sr_ghr_cpdf_temp.to_pay_table_id := l_ASGNEI_DATA.AEI_INFORMATION9;
307 --FWFA Changes
308
309 --BUG # 13532887 fetched PAY USER RA STATUS from NON SF52
310 ghr_history_fetch.fetch_asgei
311 (p_assignment_id => p_sr_assignment_id,
312 p_information_type => 'GHR_US_ASG_NON_SF52',
313 p_date_effective => p_sr_report_date,
314 p_asg_ei_data => l_non_asg_ei_data );
315
316 p_sr_ghr_cpdf_temp.pay_status := l_non_asg_ei_data.aei_information14;
317
318
319 END get_from_history_asgnei;
320
321 PROCEDURE get_from_history_people
322 (
323 p_sr_person_id IN NUMBER
324 ,p_sr_report_date IN DATE
325 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
326 )
327 IS
328
329 -- JH EHRI Added Employee Number, Email Adress
330 CURSOR PEOPLE_CUR IS
331 SELECT SEX,
332 DATE_OF_BIRTH,
333 NATIONAL_IDENTIFIER,
334 EMPLOYEE_NUMBER,
335 EMAIL_ADDRESS
336
337 FROM per_all_people
338 WHERE (TRUNC(p_sr_report_date) between effective_start_date
339 and effective_end_date)
340 AND PERSON_ID = g_person_id;
341
342 -- JH New EHRI Phones
343 CURSOR cur_phones IS
344 SELECT pho.phone_number, pho.phone_type
345 FROM per_phones pho
346 WHERE pho.parent_table = 'PER_ALL_PEOPLE_F'
347 AND pho.parent_id = g_person_id
348 AND pho.phone_type IN ('W1','H1','M');
349
350 -- JH EHRI Get Emergeny Contacts
351 CURSOR cur_contacts IS
352 SELECT contact.last_name, contact.first_name, contact.middle_names, contact.suffix
353 ,pho.phone_number, rel.date_start
354 FROM per_contact_relationships_v2 rel
355 ,per_all_people contact
356 ,per_phones pho
357 WHERE rel.person_id = g_person_id
358 AND contact_type in ('EMRG','EC')
359 AND rel.contact_person_id = contact.person_id
360 AND p_sr_report_date between contact.effective_start_date AND contact.effective_end_date
361 AND p_sr_report_date BETWEEN NVL(rel.date_start,p_sr_report_date) AND NVL(rel.date_end,p_sr_report_date)
362 AND pho.parent_id(+) = contact.person_id
363 AND p_sr_report_date BETWEEN NVL(pho.date_from,p_sr_report_date) AND NVL(pho.date_to,p_sr_report_date)
364 AND ((pho.phone_type = 'M'
365 AND NOT EXISTS (SELECT 1
366 FROM per_phones pho2
367 WHERE pho2.parent_id = pho.parent_id
368 AND pho2.phone_type IN ('H1','W1')
369 )
370 )
371 OR (pho.phone_type = 'W1'
372 AND NOT EXISTS (SELECT 1
373 FROM per_phones pho2
374 WHERE pho2.parent_id = pho.parent_id
375 AND pho2.phone_type = 'H1'
376 )
377 )
378 OR (pho.phone_type = 'H1')
379 OR (pho.phone_type IS NULL)
380 )
381 ORDER BY DECODE(rel.primary_contact_flag,'P',0,rel.sequence_number);
382
383 l_proc varchar2(30) := 'get_from_history_people';
384 l_PEOPLE_REC PEOPLE_CUR%ROWTYPE;
385 l_contact_cnt INTEGER;
386 l_cnt NUMBER := 0;
387 l_suffix GHR_CPDF_TEMP.emrgncy_cntct_suffix1%TYPE;
388 l_last_name per_all_people.last_name%type;
389
390
391 BEGIN
392 hr_utility.set_location('Entering:'||l_proc,5);
393 --Begin Bug# 4753092
394 g_message_name := 'Fetch Person Details';
395 --End Bug# 4753092
396
397 OPEN PEOPLE_CUR;
398
399 FETCH PEOPLE_CUR INTO l_PEOPLE_REC;
400
401 IF PEOPLE_CUR%FOUND
402 THEN
403 p_sr_ghr_cpdf_temp.sex := l_PEOPLE_REC.SEX;
404 p_sr_ghr_cpdf_temp.employee_date_of_birth := l_PEOPLE_REC.DATE_OF_BIRTH;
405 p_sr_ghr_cpdf_temp.to_national_identifier :=
406 SUBSTR(l_PEOPLE_REC.NATIONAL_IDENTIFIER,1,3) ||
407 SUBSTR(l_PEOPLE_REC.NATIONAL_IDENTIFIER,5,2) ||
408 SUBSTR(l_PEOPLE_REC.NATIONAL_IDENTIFIER,8,4);
409 p_sr_ghr_cpdf_temp.agency_employee_id := l_PEOPLE_REC.EMPLOYEE_NUMBER;
410 p_sr_ghr_cpdf_temp.work_employee_email := l_PEOPLE_REC.EMAIL_ADDRESS;
411
412 END IF;
413
414 CLOSE PEOPLE_CUR;
415
416 -- JH EHRI get phones.
417 --Begin Bug# 4753092
418 g_message_name := 'Fetch Person:Contact Details';
419 --End Bug# 4753092
420 FOR cur_phones_rec IN cur_phones LOOP
421 --Bug # 9817051 added substr in work address lines as it is coming as 240 from base table
422 IF cur_phones_rec.phone_type = 'W1' THEN
423 p_sr_ghr_cpdf_temp.work_phone_number := substr(cur_phones_rec.phone_number,1,35);
424 ELSIF cur_phones_rec.phone_type = 'H1' THEN
425 p_sr_ghr_cpdf_temp.home_phone_number := substr(cur_phones_rec.phone_number,1,35);
426 ELSIF cur_phones_rec.phone_type = 'M' THEN
427 p_sr_ghr_cpdf_temp.cell_phone_number := substr(cur_phones_rec.phone_number,1,35);
428 END IF;
429 END LOOP;
430
431 -- JH EHRI Emergency Contacts.
432 l_contact_cnt := 0;
433 --Begin Bug# 4753092
434 g_message_name := 'Fetch Person:Emrg Contact Dtls';
435 --End Bug# 4753092
436 FOR cur_contacts_rec IN cur_contacts LOOP
437 l_contact_cnt := l_contact_cnt +1;
438
439 IF l_contact_cnt = 1 THEN
440 -- Bug# 4648811 extracting the suffix from the lastname and also removing suffix from lastname
441 get_suffix_lname(p_last_name => cur_contacts_rec.last_name,
442 p_report_date => p_sr_report_date,
443 p_suffix => l_suffix,
444 p_lname => l_last_name);
445 p_sr_ghr_cpdf_temp.emrgncy_cntct_family_name1 := l_last_name;
446 p_sr_ghr_cpdf_temp.emrgncy_cntct_given_name1 := cur_contacts_rec.first_name;
447 p_sr_ghr_cpdf_temp.emrgncy_cntct_middle_name1 := cur_contacts_rec.middle_names;
448 p_sr_ghr_cpdf_temp.emrgncy_cntct_suffix1 := l_suffix;
449 --End Bug# 4648811
450 p_sr_ghr_cpdf_temp.emrgncy_cntct_phone1 := cur_contacts_rec.phone_number;
451 --p_sr_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt1 := fnd_date.canonical_to_date(cur_contacts_rec.date_start);
452 p_sr_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt1 := cur_contacts_rec.date_start;
453 ELSIF l_contact_cnt = 2 THEN
454 -- Bug# 4648811 extracting the suffix from the lastname and also removing suffix from lastname
455 get_suffix_lname(p_last_name => cur_contacts_rec.last_name,
456 p_report_date => p_sr_report_date,
457 p_suffix => l_suffix,
458 p_lname => l_last_name);
459 p_sr_ghr_cpdf_temp.emrgncy_cntct_family_name2 := l_last_name;
460 p_sr_ghr_cpdf_temp.emrgncy_cntct_given_name2 := cur_contacts_rec.first_name;
461 p_sr_ghr_cpdf_temp.emrgncy_cntct_middle_name2 := cur_contacts_rec.middle_names;
462 p_sr_ghr_cpdf_temp.emrgncy_cntct_suffix2 := l_suffix;
463 --End Bug# 4648811
464 p_sr_ghr_cpdf_temp.emrgncy_cntct_phone2 := cur_contacts_rec.phone_number;
465 --p_sr_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt2 := fnd_date.canonical_to_date(cur_contacts_rec.date_start);
466 p_sr_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt2 := cur_contacts_rec.date_start;
467 END IF;
468 END LOOP;
469
470 END get_from_history_people;
471
472 PROCEDURE get_from_history_ancrit
473 (
474 p_sr_person_id IN NUMBER
475 ,p_sr_report_date IN DATE
476 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
477 )
478 IS
479 l_proc varchar2(30) := 'get_from_history_ancrit';
480 l_ANCRIT_REC ghr_api.special_information_type;
481 l_emp_number per_all_people.employee_number%TYPE;
482
483 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
484 l_max_segment per_analysis_criteria.segment1%type;
485 l_lang_cnt number;
486 l_flex_struct_name varchar2(30);
487 l_special_pay_plan varchar2(30);
488 l_special_pay_grade varchar2(30);
489 l_message_name varchar2(50);
490 l_log_text varchar2(2000);
491
492 CURSOR c_per IS
493 SELECT per.employee_number
494 FROM per_all_people per
495 WHERE per.person_id = p_sr_person_id
496 AND NVL(p_sr_report_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
497 AND per.effective_end_date;
498
499 Cursor c_flex_num is
500 select flx.id_flex_num
501 from fnd_id_flex_structures_tl flx
502 where flx.id_flex_code = 'PEA' --
503 and flx.application_id = 800 --
504 and flx.id_flex_structure_name = l_flex_struct_name
505 and flx.language = 'US';
506
507 Cursor c_sit_oldest is
508 select pea.analysis_criteria_id,
509 pan.date_from, -- added for bug fix : 609285
510 pan.person_analysis_id,
511 pan.object_version_number,
512 pea.start_date_active,
513 pea.segment1,
514 pea.segment2,
515 pea.segment3,
516 pea.segment4,
517 pea.segment5,
518 pea.segment6,
519 pea.segment7
520 from per_analysis_criteria pea,
521 per_person_analyses pan
522 where pan.person_id = g_person_id
523 and pan.id_flex_num = l_id_flex_num
524 and pea.analysis_criteria_id = pan.analysis_criteria_id
525 and p_sr_report_date
526 between nvl(pan.date_from,p_sr_report_date)
527 and nvl(pan.date_to,p_sr_report_date)
528 and p_sr_report_date
529 between nvl(pea.start_date_active,p_sr_report_date)
530 and nvl(pea.end_date_active,p_sr_report_date)
531 order by 1 asc;
532
533
534 Cursor c_sit_latest is
535 select pan.date_from,
536 pan.analysis_criteria_id,
537 pac.segment3,
538 pac.segment4,
539 pac.segment5,
540 pac.segment6,
541 pac.segment17
542 from per_person_analyses pan,
543 per_analysis_Criteria pac
544 where pan.person_id = g_person_id
545 and pan.id_flex_num = l_id_flex_num
546 and pan.analysis_criteria_id = pac.analysis_criteria_id
547 and p_sr_report_date
548 between nvl(pan.date_from,p_sr_report_date)
549 and nvl(pan.date_to,p_sr_report_date)
550 and p_sr_report_date
551 between nvl(pac.start_date_active,p_sr_report_date)
552 and nvl(pac.end_date_active,p_sr_report_date)
553 order by pan.date_from desc, pan.analysis_criteria_id desc; -- Latest From Date, Most Recent Record.
554
555 BEGIN
556
557 -- bug 749386 use ghr_api.return_education_details and ghr_api.return_special_information
558 hr_utility.set_location('Entering:'||l_proc,5);
559 --Begin Bug# 4753092
560 g_message_name := 'Special Info: Education Dtls';
561 --End Bug# 4753092
562 ghr_api.return_education_details(p_person_id => p_sr_person_id,
563 p_effective_date => p_sr_report_date,
564 p_education_level => p_sr_ghr_cpdf_temp.education_level,
565 p_academic_discipline => p_sr_ghr_cpdf_temp.academic_discipline,
566 p_year_degree_attained => p_sr_ghr_cpdf_temp.year_degree_attained);
567
568 -- get language
569 --Begin Bug# 4753092
570 g_message_name := 'Special Info: Language';
571 --End Bug# 4753092
572 l_flex_struct_name := 'US Fed Language';
573 for flex_num in c_flex_num loop
574 l_id_flex_num := flex_num.id_flex_num;
575 End loop;
576
577 If l_id_flex_num is null then
578 hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
579 hr_utility.raise_error;
580 End if;
581
582 l_lang_cnt := 1;
583 FOR special_info IN c_sit_oldest LOOP
584 -- Begin Bug# 5034669
585 IF special_info.segment6 IS NOT NULL THEN
586 -- End Bug# 5034669
587 IF special_info.segment3 IS NOT NULL THEN
588 -- Reading Code 03
589 IF l_lang_cnt = 1 THEN
590 p_sr_ghr_cpdf_temp.language_code1 := special_info.segment1;
591 p_sr_ghr_cpdf_temp.lang_prof_level1 := special_info.segment3;
592 p_sr_ghr_cpdf_temp.lang_prof_type1 := '03';
593 ELSIF l_lang_cnt = 2 THEN
594 p_sr_ghr_cpdf_temp.language_code2 := special_info.segment1;
595 p_sr_ghr_cpdf_temp.lang_prof_level2 := special_info.segment3;
596 p_sr_ghr_cpdf_temp.lang_prof_type2 := '03';
597 ELSIF l_lang_cnt = 3 THEN
598 p_sr_ghr_cpdf_temp.language_code3 := special_info.segment1;
599 p_sr_ghr_cpdf_temp.lang_prof_level3 := special_info.segment3;
600 p_sr_ghr_cpdf_temp.lang_prof_type3 := '03';
601 ELSIF l_lang_cnt = 4 THEN
602 p_sr_ghr_cpdf_temp.language_code4 := special_info.segment1;
603 p_sr_ghr_cpdf_temp.lang_prof_level4 := special_info.segment3;
604 p_sr_ghr_cpdf_temp.lang_prof_type4 := '03';
605 ELSIF l_lang_cnt = 5 THEN
606 p_sr_ghr_cpdf_temp.language_code5 := special_info.segment1;
607 p_sr_ghr_cpdf_temp.lang_prof_level5 := special_info.segment3;
608 p_sr_ghr_cpdf_temp.lang_prof_type5 := '03';
609 ELSIF l_lang_cnt = 6 THEN
610 p_sr_ghr_cpdf_temp.language_code6 := special_info.segment1;
611 p_sr_ghr_cpdf_temp.lang_prof_level6 := special_info.segment3;
612 p_sr_ghr_cpdf_temp.lang_prof_type6 := '03';
613 ELSIF l_lang_cnt = 7 THEN
614 p_sr_ghr_cpdf_temp.language_code7 := special_info.segment1;
615 p_sr_ghr_cpdf_temp.lang_prof_level7 := special_info.segment3;
616 p_sr_ghr_cpdf_temp.lang_prof_type7 := '03';
617 ELSIF l_lang_cnt = 8 THEN
618 p_sr_ghr_cpdf_temp.language_code8 := special_info.segment1;
619 p_sr_ghr_cpdf_temp.lang_prof_level8 := special_info.segment3;
620 p_sr_ghr_cpdf_temp.lang_prof_type8 := '03';
621 END IF; --l_lang_cnt
622 l_lang_cnt := l_lang_cnt +1;
623 END IF; -- Reading
624 IF special_info.segment4 IS NOT NULL THEN
625 -- Speaking Code 01
626 IF l_lang_cnt = 1 THEN
627 p_sr_ghr_cpdf_temp.language_code1 := special_info.segment1;
628 p_sr_ghr_cpdf_temp.lang_prof_level1 := special_info.segment4;
629 p_sr_ghr_cpdf_temp.lang_prof_type1 := '01';
630 ELSIF l_lang_cnt = 2 THEN
631 p_sr_ghr_cpdf_temp.language_code2 := special_info.segment1;
632 p_sr_ghr_cpdf_temp.lang_prof_level2 := special_info.segment4;
633 p_sr_ghr_cpdf_temp.lang_prof_type2 := '01';
634 ELSIF l_lang_cnt = 3 THEN
635 p_sr_ghr_cpdf_temp.language_code3 := special_info.segment1;
636 p_sr_ghr_cpdf_temp.lang_prof_level3 := special_info.segment4;
637 p_sr_ghr_cpdf_temp.lang_prof_type3 := '01';
638 ELSIF l_lang_cnt = 4 THEN
639 p_sr_ghr_cpdf_temp.language_code4 := special_info.segment1;
640 p_sr_ghr_cpdf_temp.lang_prof_level4 := special_info.segment4;
641 p_sr_ghr_cpdf_temp.lang_prof_type4 := '01';
642 ELSIF l_lang_cnt = 5 THEN
643 p_sr_ghr_cpdf_temp.language_code5 := special_info.segment1;
644 p_sr_ghr_cpdf_temp.lang_prof_level5 := special_info.segment4;
645 p_sr_ghr_cpdf_temp.lang_prof_type5 := '01';
646 ELSIF l_lang_cnt = 6 THEN
647 p_sr_ghr_cpdf_temp.language_code6 := special_info.segment1;
648 p_sr_ghr_cpdf_temp.lang_prof_level6 := special_info.segment4;
649 p_sr_ghr_cpdf_temp.lang_prof_type6 := '01';
650 ELSIF l_lang_cnt = 7 THEN
651 p_sr_ghr_cpdf_temp.language_code7 := special_info.segment1;
652 p_sr_ghr_cpdf_temp.lang_prof_level7 := special_info.segment4;
653 p_sr_ghr_cpdf_temp.lang_prof_type7 := '01';
654 ELSIF l_lang_cnt = 8 THEN
655 p_sr_ghr_cpdf_temp.language_code8 := special_info.segment1;
656 p_sr_ghr_cpdf_temp.lang_prof_level8 := special_info.segment4;
657 p_sr_ghr_cpdf_temp.lang_prof_type8 := '01';
658 END IF; --l_lang_cnt
659 l_lang_cnt := l_lang_cnt +1;
660 END IF; -- Speaking
661 IF special_info.segment5 IS NOT NULL THEN
662 -- Listening Code 02
663 IF l_lang_cnt = 1 THEN
664 p_sr_ghr_cpdf_temp.language_code1 := special_info.segment1;
665 p_sr_ghr_cpdf_temp.lang_prof_level1 := special_info.segment5;
666 p_sr_ghr_cpdf_temp.lang_prof_type1 := '02';
667 ELSIF l_lang_cnt = 2 THEN
668 p_sr_ghr_cpdf_temp.language_code2 := special_info.segment1;
669 p_sr_ghr_cpdf_temp.lang_prof_level2 := special_info.segment5;
670 p_sr_ghr_cpdf_temp.lang_prof_type2 := '02';
671 ELSIF l_lang_cnt = 3 THEN
672 p_sr_ghr_cpdf_temp.language_code3 := special_info.segment1;
673 p_sr_ghr_cpdf_temp.lang_prof_level3 := special_info.segment5;
674 p_sr_ghr_cpdf_temp.lang_prof_type3 := '02';
675 ELSIF l_lang_cnt = 4 THEN
676 p_sr_ghr_cpdf_temp.language_code4 := special_info.segment1;
677 p_sr_ghr_cpdf_temp.lang_prof_level4 := special_info.segment5;
678 p_sr_ghr_cpdf_temp.lang_prof_type4 := '02';
679 ELSIF l_lang_cnt = 5 THEN
680 p_sr_ghr_cpdf_temp.language_code5 := special_info.segment1;
681 p_sr_ghr_cpdf_temp.lang_prof_level5 := special_info.segment5;
682 p_sr_ghr_cpdf_temp.lang_prof_type5 := '02';
683 ELSIF l_lang_cnt = 6 THEN
684 p_sr_ghr_cpdf_temp.language_code6 := special_info.segment1;
685 p_sr_ghr_cpdf_temp.lang_prof_level6 := special_info.segment5;
686 p_sr_ghr_cpdf_temp.lang_prof_type6 := '02';
687 ELSIF l_lang_cnt = 7 THEN
688 p_sr_ghr_cpdf_temp.language_code7 := special_info.segment1;
689 p_sr_ghr_cpdf_temp.lang_prof_level7 := special_info.segment5;
690 p_sr_ghr_cpdf_temp.lang_prof_type7 := '02';
691 ELSIF l_lang_cnt = 8 THEN
692 p_sr_ghr_cpdf_temp.language_code8 := special_info.segment1;
693 p_sr_ghr_cpdf_temp.lang_prof_level8 := special_info.segment5;
694 p_sr_ghr_cpdf_temp.lang_prof_type8 := '02';
695 END IF; --l_lang_cnt
696 l_lang_cnt := l_lang_cnt +1;
697 END IF; -- Listening
698 IF special_info.segment7 IS NOT NULL THEN
699 -- Writing Code 04
700 IF l_lang_cnt = 1 THEN
701 p_sr_ghr_cpdf_temp.language_code1 := special_info.segment1;
702 p_sr_ghr_cpdf_temp.lang_prof_level1 := special_info.segment7;
703 p_sr_ghr_cpdf_temp.lang_prof_type1 := '04';
704 ELSIF l_lang_cnt = 2 THEN
705 p_sr_ghr_cpdf_temp.language_code2 := special_info.segment1;
706 p_sr_ghr_cpdf_temp.lang_prof_level2 := special_info.segment7;
707 p_sr_ghr_cpdf_temp.lang_prof_type2 := '04';
708 ELSIF l_lang_cnt = 3 THEN
709 p_sr_ghr_cpdf_temp.language_code3 := special_info.segment1;
710 p_sr_ghr_cpdf_temp.lang_prof_level3 := special_info.segment7;
711 p_sr_ghr_cpdf_temp.lang_prof_type3 := '04';
712 ELSIF l_lang_cnt = 4 THEN
713 p_sr_ghr_cpdf_temp.language_code4 := special_info.segment1;
714 p_sr_ghr_cpdf_temp.lang_prof_level4 := special_info.segment7;
715 p_sr_ghr_cpdf_temp.lang_prof_type4 := '04';
716 ELSIF l_lang_cnt = 5 THEN
717 p_sr_ghr_cpdf_temp.language_code5 := special_info.segment1;
718 p_sr_ghr_cpdf_temp.lang_prof_level5 := special_info.segment7;
719 p_sr_ghr_cpdf_temp.lang_prof_type5 := '04';
720 ELSIF l_lang_cnt = 6 THEN
721 p_sr_ghr_cpdf_temp.language_code6 := special_info.segment1;
722 p_sr_ghr_cpdf_temp.lang_prof_level6 := special_info.segment7;
723 p_sr_ghr_cpdf_temp.lang_prof_type6 := '04';
724 ELSIF l_lang_cnt = 7 THEN
725 p_sr_ghr_cpdf_temp.language_code7 := special_info.segment1;
726 p_sr_ghr_cpdf_temp.lang_prof_level7 := special_info.segment7;
727 p_sr_ghr_cpdf_temp.lang_prof_type7 := '04';
728 ELSIF l_lang_cnt = 8 THEN
729 p_sr_ghr_cpdf_temp.language_code8 := special_info.segment1;
730 p_sr_ghr_cpdf_temp.lang_prof_level8 := special_info.segment7;
731 p_sr_ghr_cpdf_temp.lang_prof_type8 := '04';
732 END IF; --l_lang_cnt
733 l_lang_cnt := l_lang_cnt +1;
734 END IF; -- Writing
735 -- Begin Bug# 5034669
736 END IF; -- Segment6
737 -- End Bug# 5034669
738 IF l_lang_cnt >= 9 THEN
739 EXIT;
740 END IF;
741 END LOOP; -- Language
742
743 -- get Performance Appraisal Details
744 l_flex_struct_name := 'US Fed Perf Appraisal';
745 --Begin Bug# 4753092
746 g_message_name := 'Special Info: Perf Appraisal';
747 --End Bug# 4753092
748 for flex_num in c_flex_num loop
749 l_id_flex_num := flex_num.id_flex_num;
750 End loop;
751
752 If l_id_flex_num is null then
753 hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
754 hr_utility.raise_error;
755 End if;
756
757 l_lang_cnt := 0;
758 FOR special_info IN c_sit_latest LOOP
759 p_sr_ghr_cpdf_temp.rating_of_record_pattern := special_info.segment4;
760 p_sr_ghr_cpdf_temp.rating_of_record_level := special_info.segment5;
761 p_sr_ghr_cpdf_temp.rating_of_record_period_ends := fnd_date.canonical_to_date(special_info.segment6);
762 --Bug# 4753117 05-MAR-07 Veeramani adding Appraisal start date
763 p_sr_ghr_cpdf_temp.rating_of_record_period_starts := fnd_date.canonical_to_date(special_info.segment17);
764 l_lang_cnt := l_lang_cnt + 1;
765 exit;
766 END LOOP;
767
768 END get_from_history_ancrit;
769
770 PROCEDURE get_from_history_peopei
771 (
772 p_sr_person_id IN NUMBER
773 ,p_sr_report_date IN DATE
774 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
775 )
776 IS
777
778 -- JH EHRI may need date factored in and order by (test)
779 Cursor c_extra_info_id is
780 select pei.person_extra_info_id
781 from per_people_extra_info pei
782 where pei.person_id = p_sr_person_id
783 and pei.information_type = 'GHR_US_PER_SERVICE_OBLIGATION'
784 order by pei.person_extra_info_id;
785
786 Cursor c_retained_pay_table_name (p_user_table_id number) is
787 select substr(user_table_name,1,4) user_table_name
788 from pay_user_tables
789 where user_table_id = p_user_table_id;
790
791
792 l_proc varchar2(30) := 'get_from_history_peopei';
793 l_PEOPEI_DATA PER_PEOPLE_EXTRA_INFO%ROWTYPE;
794 l_PEOPEI_DATA_INIT PER_PEOPLE_EXTRA_INFO%ROWTYPE;
795 l_type_of_employment per_people_extra_info.pei_information4%TYPE;
796 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
797 l_session ghr_history_api.g_session_var_type;
798 l_extra_info_id per_people_extra_info.person_extra_info_id%type;
799 l_result varchar2(20);
800 l_cnt number;
801 l_user_table_id number;
802 l_message_name varchar2(50);
803 l_log_text varchar2(2000);
804
805 BEGIN
806 hr_utility.set_location('Entering:'||l_proc,5);
807 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
808 -- Begin Bug# 4753092
809 g_message_name := 'Person EIT: Uniformed Serivces';
810 -- End Bug# 4753092
811 GHR_HISTORY_FETCH.fetch_peopleei(
812 p_person_id => p_sr_person_id,
813 p_information_type => 'GHR_US_PER_UNIFORMED_SERVICES',
814 p_date_effective => p_sr_report_date,
815 p_per_ei_data => l_PEOPEI_DATA
816 );
817 p_sr_ghr_cpdf_temp.creditable_military_service := l_PEOPEI_DATA.PEI_INFORMATION5;
818 --Begin Bug# 4672725
819 IF l_PEOPEI_DATA.PEI_INFORMATION20 IS NOT NULL THEN
820 p_sr_ghr_cpdf_temp.mil_svc_end_date := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION20);
821 ELSE
822 p_sr_ghr_cpdf_temp.mil_svc_end_date := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION6);
823 END IF;
824 --end Bug# 4672725
825 p_sr_ghr_cpdf_temp.act_svc_indicator := l_PEOPEI_DATA.PEI_INFORMATION12;
826 p_sr_ghr_cpdf_temp.mil_char_svc_code := l_PEOPEI_DATA.PEI_INFORMATION13;
827 p_sr_ghr_cpdf_temp.mil_svc_sno := l_PEOPEI_DATA.PEI_INFORMATION14;
828 p_sr_ghr_cpdf_temp.mil_svc_start_date := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION15);
829 p_sr_ghr_cpdf_temp.mil_branch_code := l_PEOPEI_DATA.PEI_INFORMATION16;
830 p_sr_ghr_cpdf_temp.mil_discharge_code := l_PEOPEI_DATA.PEI_INFORMATION17;
831 --Bug# 15999091
832 p_sr_ghr_cpdf_temp.slct_serv_regi_indicator := l_PEOPEI_DATA.PEI_INFORMATION18;
833
834
835 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
836 -- Begin Bug# 4753092
837 g_message_name := 'Person EIT: Separation, Retire';
838 -- End Bug# 4753092
839 GHR_HISTORY_FETCH.fetch_peopleei(
840 p_person_id => p_sr_person_id,
841 p_information_type => 'GHR_US_PER_SEPARATE_RETIRE',
842 p_date_effective => p_sr_report_date,
843 p_per_ei_data => l_PEOPEI_DATA
844 );
845 p_sr_ghr_cpdf_temp.frozen_service := l_PEOPEI_DATA.PEI_INFORMATION5;
846 p_sr_ghr_cpdf_temp.fers_coverage := l_PEOPEI_DATA.PEI_INFORMATION3;
847 p_sr_ghr_cpdf_temp.fers_elect_date := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION18);
848 p_sr_ghr_cpdf_temp.fers_elect_indicator := l_PEOPEI_DATA.PEI_INFORMATION19;
849 p_sr_ghr_cpdf_temp.disability_retire_notify := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION20);
850
851 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
852 -- Begin Bug# 4753092
853 g_message_name := 'Person EIT: Person RPA';
854 -- End Bug# 4753092
855 GHR_HISTORY_FETCH.fetch_peopleei(
856 p_person_id => p_sr_person_id,
857 p_information_type => 'GHR_US_PER_SF52',
858 p_date_effective => p_sr_report_date,
859 p_per_ei_data => l_PEOPEI_DATA
860 );
861
862 p_sr_ghr_cpdf_temp.veterans_preference := l_PEOPEI_DATA.PEI_INFORMATION4;
863 p_sr_ghr_cpdf_temp.veterans_status := l_PEOPEI_DATA.PEI_INFORMATION6;
864 -- Bug#5063292 Show the citizenship as 'Y' for value '1'
865 -- 'N' for '8' and 'NA' for NULL
866 IF l_peopei_data.pei_information3 is NOT NULL THEN
867 IF l_peopei_data.pei_information3 = '1' THEN
868 p_sr_ghr_cpdf_temp.citizenship := 'Y';
869 ELSIF l_peopei_data.pei_information3 = '8' THEN
870 p_sr_ghr_cpdf_temp.citizenship := 'N';
871 ELSE
872 p_sr_ghr_cpdf_temp.citizenship := l_peopei_data.pei_information3;
873 END IF;
874 ELSE
875 p_sr_ghr_cpdf_temp.citizenship := 'NA';
876 END IF;
877
878 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
879 -- Begin Bug# 4753092
880 g_message_name := 'Person EIT: Person Group1';
881 -- End Bug# 4753092
882 GHR_HISTORY_FETCH.fetch_peopleei(
883 p_person_id => p_sr_person_id,
884 p_information_type => 'GHR_US_PER_GROUP1',
885 p_date_effective => p_sr_report_date,
886 p_per_ei_data => l_PEOPEI_DATA
887 );
888 --Bug # 12871099 Modified to show only first 3 characters
889 p_sr_ghr_cpdf_temp.current_appointment_auth1 := substr(l_PEOPEI_DATA.PEI_INFORMATION8,1,3);
890 p_sr_ghr_cpdf_temp.current_appointment_auth2 := substr(l_PEOPEI_DATA.PEI_INFORMATION9,1,3);
891 --Bug # 12871099
892 p_sr_ghr_cpdf_temp.race_national_origin := l_PEOPEI_DATA.PEI_INFORMATION5;
893 p_sr_ghr_cpdf_temp.handicap_code := l_PEOPEI_DATA.PEI_INFORMATION11;
894 l_type_of_employment := l_PEOPEI_DATA.PEI_INFORMATION4;
895 p_sr_ghr_cpdf_temp.world_citizenship := l_PEOPEI_DATA.PEI_INFORMATION10;
896 -- Bug#5184166 Appointment type code should not be reported.
897 -- p_sr_ghr_cpdf_temp.appoint_type_code := l_PEOPEI_DATA.PEI_INFORMATION3;
898 p_sr_ghr_cpdf_temp.ehri_employee_id := to_number(l_PEOPEI_DATA.PEI_INFORMATION18);
899 --Bug# 15999091, commented below code since slct_serv_regi_indicator should fetch from
900 -- EIT GHR_US_PER_UNIFORMED_SERVICES not from GHR_US_PER_GROUP1
901 --p_sr_ghr_cpdf_temp.slct_serv_regi_indicator := l_PEOPEI_DATA.PEI_INFORMATION19;
902 p_sr_ghr_cpdf_temp.career_tenure_code := l_PEOPEI_DATA.PEI_INFORMATION20;
903
904 -- bug 749190 Use FUNCTION ghr_pc_basic_pay.get_retained_grade_details instead of
905 -- GHR_HISTORY_FETCH.fetch_peopleei
906 -- do not worry if it didn't return anything!
907 -- Begin Bug# 4753092
908 g_message_name := 'Person EIT: RG Details';
909 -- End Bug# 4753092
910 -- Bug# 4753092 Added If Condition
911 IF p_sr_ghr_cpdf_temp.to_pay_rate_determinant IN ('A','B','E','F','U','V') THEN
912 BEGIN
913 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details (
914 p_person_id => p_sr_person_id,
915 p_effective_date => p_sr_report_date
916 );
917 --- added for bug 3834462 Madhuri, store Retained Pay Basis value
918 --- Start of fix
919 p_sr_ghr_cpdf_temp.to_pay_basis := l_retained_grade_rec.pay_basis;
920 -- End of Bug fix
921 p_sr_ghr_cpdf_temp.retained_pay_plan := l_retained_grade_rec.pay_plan;
922 p_sr_ghr_cpdf_temp.retained_grade_or_level := l_retained_grade_rec.grade_or_level;
923 p_sr_ghr_cpdf_temp.retained_step_or_rate := l_retained_grade_rec.step_or_rate;
924
925 -- JH Added for EHRI to get retained pay table name.
926 l_user_table_id := l_retained_grade_rec.user_table_id;
927 g_retained_pay_table_name := NULL;
928 For retained_pay in c_retained_pay_table_name(l_user_table_id) loop
929 g_retained_pay_table_name := retained_pay.user_table_name;
930 End Loop;
931
932 EXCEPTION
933 WHEN ghr_pay_calc.pay_calc_message THEN
934 NULL;
935 END;
936 END IF;
937 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
938 -- Begin Bug# 4753092
939 g_message_name := 'Person EIT: Person SCD Info';
940 -- End Bug# 4753092
941 GHR_HISTORY_FETCH.fetch_peopleei(
942 p_person_id => p_sr_person_id,
943 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
944 p_date_effective => p_sr_report_date,
945 p_per_ei_data => l_PEOPEI_DATA
946 );
947 -- SVC may be changed w/ resolution of type/length conversion
948 p_sr_ghr_cpdf_temp.service_comp_date :=
949 fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION3);
950
951 -- SCD RIF and Retirement dates
952 p_sr_ghr_cpdf_temp.SCD_rif :=
953 fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION5);
954 p_sr_ghr_cpdf_temp.SCD_retirement :=
955 fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION7);
956
957 p_sr_ghr_cpdf_temp.leave_scd := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION3);
958 p_sr_ghr_cpdf_temp.tsp_scd := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION6);
959 p_sr_ghr_cpdf_temp.scd_ses := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION8);
960 p_sr_ghr_cpdf_temp.scd_spcl_retire := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION9);
961
962 -- JH EHRI Multiple Entries!!! GHR_US_PER_SERVICE_OBLIGATION
963 l_cnt := 0;
964 --Bug # 9817051 initialize the l_peopei_data with initial values
965
966 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
967 -- Begin Bug# 4753092
968 g_message_name := 'Person EIT: Service Obligation';
969 -- End Bug# 4753092
970 For extra_info in c_extra_info_id loop
971 l_cnt := l_cnt + 1;
972 l_extra_info_id := extra_info.person_extra_info_id;
973 If l_extra_info_id is not null then
974 hr_utility.set_location(l_proc,10);
975 ghr_history_api.get_g_session_var(l_session);
976 ghr_history_fetch.fetch_peopleei ( p_person_extra_info_id => l_extra_info_id,
977 p_date_effective => p_sr_report_date,
978 p_altered_pa_request_id => l_session.altered_pa_request_id,
979 p_noa_id_corrected => l_session.noa_id_correct,
980 p_pa_history_id => l_session.pa_history_id,
981 p_peopleei_data => l_PEOPEI_DATA,
982 p_get_ovn_flag => 'Y',
983 p_result_code => l_result
984 );
985
986 IF l_cnt = 1 THEN
987 p_sr_ghr_cpdf_temp.svc_oblig_type_code1 := l_PEOPEI_DATA.PEI_INFORMATION3;
988 p_sr_ghr_cpdf_temp.svc_oblig_type_end_date1 := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION4);
989 ELSIF l_cnt = 2 THEN
990 p_sr_ghr_cpdf_temp.svc_oblig_type_code2 := l_PEOPEI_DATA.PEI_INFORMATION3;
991 p_sr_ghr_cpdf_temp.svc_oblig_type_end_date2 := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION4);
992 ELSIF l_cnt = 3 THEN
993 p_sr_ghr_cpdf_temp.svc_oblig_type_code3 := l_PEOPEI_DATA.PEI_INFORMATION3;
994 p_sr_ghr_cpdf_temp.svc_oblig_type_end_date3 := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION4);
995 ELSIF l_cnt = 4 THEN
996 p_sr_ghr_cpdf_temp.svc_oblig_type_code4 := l_PEOPEI_DATA.PEI_INFORMATION3;
997 p_sr_ghr_cpdf_temp.svc_oblig_type_end_date4 := fnd_date.canonical_to_date(l_PEOPEI_DATA.PEI_INFORMATION4);
998 END IF;
999 End if;
1000 End loop;
1001
1002 -- CPDF EDITS FOR CREDITABLE MILITARY SERVICE
1003 -- October date specified per requirements
1004 -- Bug# 4060669 starts
1005 -- Modified with the requirements specified in the bug 12394245
1006 IF NVL(p_sr_ghr_cpdf_temp.annuitant_indicator,'9') NOT IN ('2','3','9') OR
1007 g_appointment_date < to_date('1986/10/01','YYYY/MM/DD') THEN
1008 p_sr_ghr_cpdf_temp.creditable_military_service := NULL;
1009 ELSIF NVL(p_sr_ghr_cpdf_temp.annuitant_indicator,'9') IN ('2','3','9') AND
1010 g_appointment_date >= to_date('1986/10/01','YYYY/MM/DD') AND
1011 p_sr_ghr_cpdf_temp.creditable_military_service IS NULL THEN
1012 p_sr_ghr_cpdf_temp.creditable_military_service := '000000';
1013 END IF;
1014
1015 /*
1016 -- Bug# 4060669 ends
1017 IF (SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,1,1) < '0' OR
1018 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,2,1) < '0' OR
1019 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,3,1) < '0' OR
1020 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,4,1) < '0' OR
1021 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,5,1) < '0' OR -- need to be added for ehri
1022 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,6,1) < '0' OR -- need to be added for ehri
1023 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,1,1) > '9' OR
1024 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,2,1) > '9' OR
1025 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,3,1) > '9' OR
1026 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,4,1) > '9' OR
1027 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,5,1) > '9' OR -- need to be added for ehri
1028 SUBSTR(p_sr_ghr_cpdf_temp.creditable_military_service,6,1) > '9' ) -- need to be added for ehri
1029 AND
1030 p_sr_ghr_cpdf_temp.creditable_military_service IS NOT NULL AND
1031 p_sr_ghr_cpdf_temp.creditable_military_service <> ' '
1032 THEN
1033 p_sr_ghr_cpdf_temp.creditable_military_service := '000000'; -- from 0000 to '000000'
1034 END IF;
1035 */
1036
1037
1038 -- CPDF EDITS FOR FROZEN SERVICE
1039 -- October date specified per requirements
1040 -- use retirement plan, not fers coverage per raj/john
1041 /* IF p_sr_ghr_cpdf_temp.retirement_plan NOT IN('K','L','M','N','C','E') OR
1042 g_appointment_date < to_date('1986/10/01','YYYY/MM/DD')
1043 THEN
1044 p_sr_ghr_cpdf_temp.frozen_service := ' ';
1045 END IF; */
1046
1047 --Bug # 12535772 Frozen service will be displayed when retirement Plan is K,L,M,N or Retirement Plan in C, E
1048 -- and Hire Date is later than 01-July-1987
1049 --Bug# 15941036 added Retirement codes KR,LR,MR,NR
1050 IF p_sr_ghr_cpdf_temp.retirement_plan IN ('K','L','M','N','KR','LR','MR','NR') OR (p_sr_ghr_cpdf_temp.retirement_plan IN ('C','E') AND
1051 g_appointment_date >= to_date('1987/07/01','YYYY/MM/DD')) THEN
1052 IF p_sr_ghr_cpdf_temp.frozen_service is NULL THEN
1053 p_sr_ghr_cpdf_temp.frozen_service := '000000';
1054 END IF;
1055 ELSE
1056 p_sr_ghr_cpdf_temp.frozen_service := ' ';
1057 END IF;
1058
1059
1060
1061 -- CPDF EDITS FOR FERS COVERAGE
1062 -- use retirement plan, not fers coverage per raj/john
1063 --Bug# 15941036 added Retirement codes KR,LR,MR,NR
1064 IF p_sr_ghr_cpdf_temp.retirement_plan NOT IN('K','L','M','N','KR','LR','MR','NR')
1065 THEN
1066 p_sr_ghr_cpdf_temp.fers_coverage := ' ';
1067 END IF;
1068
1069 -- CPDF EDITS FOR RETAINED ...
1070 IF p_sr_ghr_cpdf_temp.to_pay_rate_determinant
1071 NOT IN ('A','B','E','F','U','V')
1072 THEN
1073 p_sr_ghr_cpdf_temp.retained_pay_plan := NULL;
1074 p_sr_ghr_cpdf_temp.retained_grade_or_level := NULL;
1075 p_sr_ghr_cpdf_temp.retained_step_or_rate := NULL;
1076 -- Bug 3834462 fix Madhuri
1077 -- NULL this out incase the PRD is not in above list,
1078 -- pay basis can be picked now from valid grade info instead of the retained grade details.
1079 p_sr_ghr_cpdf_temp.to_pay_basis := NULL;
1080 END IF;
1081
1082 /*
1083 -- Populating based on Assignment Status for EHRI
1084 -- CPDF EDITS FOR PAY_STATUS
1085 -- pay status is derived from type of employment per logic provided
1086 -- by john z.
1087
1088 IF l_type_of_employment
1089 IN ('1','2','3','4','5','6','7','C','D','E','G','H','J','W')
1090 THEN
1091 p_sr_ghr_cpdf_temp.pay_status := 'P';
1092 ELSIF l_type_of_employment = 'F'
1093 THEN
1094 p_sr_ghr_cpdf_temp.pay_status := 'N';
1095 ELSE
1096 p_sr_ghr_cpdf_temp.pay_status := l_type_of_employment;
1097 END IF;
1098 */
1099
1100
1101 -- Fetching Race and ethnicity category
1102 l_PEOPEI_DATA :=NULL;
1103 -- Begin Bug# 4753092
1104 g_message_name := 'Person EIT: Ethnicity, Race';
1105
1106 -- End Bug# 4753092
1107 ghr_history_fetch.fetch_peopleei
1108 (p_person_id => p_sr_person_id,
1109 p_information_type => 'GHR_US_PER_ETHNICITY_RACE',
1110 p_date_effective => p_sr_report_date,
1111 p_per_ei_data => l_PEOPEI_DATA
1112 );
1113
1114 p_sr_ghr_cpdf_temp.race_ethnic_info := NULL;
1115 -- Populate Race only if atleast one data segment is entered.
1116 IF l_PEOPEI_DATA.pei_information3 IS NOT NULL OR
1117 l_PEOPEI_DATA.pei_information4 IS NOT NULL OR
1118 l_PEOPEI_DATA.pei_information5 IS NOT NULL OR
1119 l_PEOPEI_DATA.pei_information6 IS NOT NULL OR
1120 l_PEOPEI_DATA.pei_information7 IS NOT NULL OR
1121 l_PEOPEI_DATA.pei_information8 IS NOT NULL THEN
1122 p_sr_ghr_cpdf_temp.race_ethnic_info := NVL(l_PEOPEI_DATA.pei_information3,'0') || NVL(l_PEOPEI_DATA.pei_information4,'0') || NVL(l_PEOPEI_DATA.pei_information5,'0') ||
1123 NVL(l_PEOPEI_DATA.pei_information6,'0') || NVL(l_PEOPEI_DATA.pei_information7,'0') || NVL(l_PEOPEI_DATA.pei_information8,'0');
1124 END IF;
1125 -- End Bug 4714292 EHRI Reports Changes for EOY 05
1126
1127 --Begin Bug# 6158983
1128
1129
1130 l_PEOPEI_DATA := l_PEOPEI_DATA_INIT;
1131 g_message_name := 'Person EIT : US Benefit Cont';
1132
1133 GHR_HISTORY_FETCH.fetch_peopleei(p_person_id => p_sr_person_id,
1134 p_information_type => 'GHR_US_PER_BENEFITS_CONT',
1135 p_date_effective => p_sr_report_date,
1136 p_per_ei_data => l_PEOPEI_DATA
1137 );
1138
1139
1140 p_sr_ghr_cpdf_temp.fegli_indicator := l_peopei_data.pei_information1;
1141 p_sr_ghr_cpdf_temp.fegli_elect_date := fnd_date.canonical_to_date(l_peopei_data.pei_information2);
1142 p_sr_ghr_cpdf_temp.fegli_notify_date := fnd_date.canonical_to_date(l_peopei_data.pei_information3);
1143 p_sr_ghr_cpdf_temp.fehb_indicator := l_peopei_data.pei_information4;
1144 p_sr_ghr_cpdf_temp.fehb_elect_date := fnd_date.canonical_to_date(l_peopei_data.pei_information5);
1145 p_sr_ghr_cpdf_temp.fehb_notify_date := fnd_date.canonical_to_date(l_peopei_data.pei_information6);
1146 p_sr_ghr_cpdf_temp.retire_indicator := l_peopei_data.pei_information7;
1147 p_sr_ghr_cpdf_temp.retire_elect_date := fnd_date.canonical_to_date(l_peopei_data.pei_information12);
1148 p_sr_ghr_cpdf_temp.retire_notify_date := fnd_date.canonical_to_date(l_peopei_data.pei_information8);
1149 p_sr_ghr_cpdf_temp.cont_term_elect_date := fnd_date.canonical_to_date(l_peopei_data.pei_information9);
1150 p_sr_ghr_cpdf_temp.cont_ins_pay_notify_date:= fnd_date.canonical_to_date(l_peopei_data.pei_information10);
1151 p_sr_ghr_cpdf_temp.cont_pay_type_code:= l_peopei_data.pei_information11;
1152
1153 -- End Bug#6158983
1154
1155 END get_from_history_peopei;
1156
1157 PROCEDURE get_from_history_posiei
1158 (
1159 p_sr_position_id IN NUMBER
1160 ,p_sr_report_date IN DATE
1161 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
1162 )
1163 IS
1164 l_proc varchar2(30) := 'get_from_history_posiei';
1165 l_POSIEI_DATA PER_POSITION_EXTRA_INFO%ROWTYPE;
1166 l_POSIEI_DATA_INIT PER_POSITION_EXTRA_INFO%ROWTYPE;
1167 l_to_pay_table_id ghr_cpdf_temp.to_pay_table_id%type;
1168 BEGIN
1169 hr_utility.set_location('Entering:'||l_proc,5);
1170
1171 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
1172 -- Begin Bug# 4753092
1173 g_message_name := 'Position EIT: Position Group1';
1174 -- End Bug# 4753092
1175 GHR_HISTORY_FETCH.fetch_positionei(
1176 p_position_id => p_sr_position_id,
1177 p_information_type => 'GHR_US_POS_GRP1',
1178 p_date_effective => p_sr_report_date,
1179 p_pos_ei_data => l_POSIEI_DATA
1180 );
1181 -- Bug# 4753092. As EHRI Status report is having lentgh 18 for Organizational component
1182 -- to avoid the errors we use the substr.
1183 p_sr_ghr_cpdf_temp.organizational_component := substr(l_POSIEI_DATA.POEI_INFORMATION5,1,30);
1184 p_sr_ghr_cpdf_temp.personnel_office_id := l_POSIEI_DATA.POEI_INFORMATION3;
1185 p_sr_ghr_cpdf_temp.functional_class := l_POSIEI_DATA.POEI_INFORMATION11;
1186 p_sr_ghr_cpdf_temp.supervisory_status := l_POSIEI_DATA.POEI_INFORMATION16;
1187 p_sr_ghr_cpdf_temp.flsa_category := l_POSIEI_DATA.POEI_INFORMATION7;
1188 p_sr_ghr_cpdf_temp.bargaining_unit_status := SUBSTR(l_POSIEI_DATA.POEI_INFORMATION8,length(l_POSIEI_DATA.POEI_INFORMATION8)-3);
1189
1190
1191 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
1192 -- Begin Bug# 4753092
1193 g_message_name := 'Position EIT: Valid Grade';
1194 -- End Bug# 4753092
1195 GHR_HISTORY_FETCH.fetch_positionei(
1196 p_position_id => p_sr_position_id,
1197 p_information_type => 'GHR_US_POS_VALID_GRADE',
1198 p_date_effective => p_sr_report_date,
1199 p_pos_ei_data => l_POSIEI_DATA
1200 );
1201 -- Added this condition for Bug 3834462 Fix (Madhuri)
1202 IF p_sr_ghr_cpdf_temp.to_pay_basis is NULL THEN
1203 p_sr_ghr_cpdf_temp.to_pay_basis := l_POSIEI_DATA.POEI_INFORMATION6;
1204 END IF;
1205 -- Added this condition for Bug 3834462 Fix (Madhuri)
1206 g_pay_table_name := null;
1207 if (l_POSIEI_DATA.POEI_INFORMATION5 is not null) then
1208 select substr(user_table_name,1,4)
1209 into l_to_pay_table_id
1210 from pay_user_tables
1211 where user_table_id = l_POSIEI_DATA.POEI_INFORMATION5;
1212 g_pay_table_name := l_to_pay_table_id;
1213 end if;
1214
1215 -- JH EHRI Special Salary Rate
1216 IF g_ghr_cpdf_temp.to_pay_rate_determinant in ('E','F','U','V')
1217 AND g_ghr_cpdf_temp.retained_pay_plan in ('GS','GG','GM','GH')
1218 AND nvl(g_retained_pay_table_name,'0000') <> '0000' THEN
1219 g_ghr_cpdf_temp.spcl_salary_rate := g_ghr_cpdf_temp.to_basic_pay;
1220 ELSIF g_ghr_cpdf_temp.to_pay_rate_determinant in ('6','7')
1221 AND nvl(g_pay_table_name,'0000') <> '0000' THEN
1222 g_ghr_cpdf_temp.spcl_salary_rate := g_ghr_cpdf_temp.to_basic_pay;
1223 ELSE
1224 g_ghr_cpdf_temp.spcl_salary_rate := NULL;
1225 END IF;
1226
1227
1228 -- JH Special Pay Table ID, if PRD = 6 and Pay Plan equal GS or GG then populate with Pay Table ID
1229 -- or PRD is E or F and Retained Pay Plan = GS or GG then populate with Retained Grade Pay Table ID.
1230
1231 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
1232 -- Begin Bug# 4753092
1233 g_message_name := 'Position EIT: Position Group2';
1234 -- End Bug# 4753092
1235 GHR_HISTORY_FETCH.fetch_positionei(
1236 p_position_id => p_sr_position_id,
1237 p_information_type => 'GHR_US_POS_GRP2',
1238 p_date_effective => p_sr_report_date,
1239 p_pos_ei_data => l_POSIEI_DATA
1240 );
1241 p_sr_ghr_cpdf_temp.position_occupied := l_POSIEI_DATA.POEI_INFORMATION3;
1242 p_sr_ghr_cpdf_temp.appropriation_code := l_POSIEI_DATA.POEI_INFORMATION13;
1243 p_sr_ghr_cpdf_temp.comp_pos_indicator := l_POSIEI_DATA.POEI_INFORMATION18;
1244
1245 -- CPDF EDITS FOR PAY TABLE ID
1246 IF p_sr_ghr_cpdf_temp.to_pay_rate_determinant NOT IN ('5','6','E','F','M')
1247 THEN
1248 p_sr_ghr_cpdf_temp.to_pay_table_id := ' ';
1249 END IF;
1250 --Begin bug# 14245991
1251 l_POSIEI_DATA := l_POSIEI_DATA_INIT;
1252 g_message_name := 'Position EIT: Telework Info';
1253 GHR_HISTORY_FETCH.fetch_positionei(
1254 p_position_id => p_sr_position_id,
1255 p_information_type => 'GHR_US_POS_TELEWORK_INFO',
1256 p_date_effective => p_sr_report_date,
1257 p_pos_ei_data => l_POSIEI_DATA
1258 );
1259 p_sr_ghr_cpdf_temp.Telework_Indicator := l_POSIEI_DATA.POEI_INFORMATION1;
1260 --End bug# 14245991
1261
1262 END get_from_history_posiei;
1263
1264 PROCEDURE get_from_history_gradef
1265 (
1266 p_sr_grade_id IN NUMBER
1267 ,p_sr_report_date IN DATE
1268 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
1269 )
1270 IS
1271 -- start_date_active and end_date_active on the PER_GRADE_DEFINITIONS
1272 -- table WAS NOT considered as a query criteria because all rows on
1273 -- GHRDEV16 had null values for both columns.
1274 l_proc varchar2(30) := 'get_from_history_gradef';
1275 CURSOR GRADEFCUR IS
1276 SELECT SEGMENT1,
1277 SEGMENT2
1278 FROM PER_GRADE_DEFINITIONS
1279 WHERE GRADE_DEFINITION_ID =
1280 (SELECT MAX(GRADE_DEFINITION_ID)
1281 FROM PER_GRADES
1282 WHERE GRADE_ID = g_grade_id);
1283
1284 l_GRADEFREC GRADEFCUR%ROWTYPE;
1285 BEGIN
1286 hr_utility.set_location('Entering:'||l_proc,5);
1287
1288 OPEN GRADEFCUR;
1289
1290 FETCH GRADEFCUR INTO l_GRADEFREC;
1291
1292 -- Pay Plan to be changed w/ resolution of type/length conversion
1293
1294 IF GRADEFCUR%FOUND
1295 THEN
1296 p_sr_ghr_cpdf_temp.to_pay_plan := substr(l_GRADEFREC.SEGMENT1,1,2);
1297 g_to_pay_plan := p_sr_ghr_cpdf_temp.to_pay_plan;
1298 p_sr_ghr_cpdf_temp.to_grade_or_level := l_GRADEFREC.SEGMENT2;
1299 END IF;
1300
1301 CLOSE GRADEFCUR;
1302 END get_from_history_gradef;
1303
1304 PROCEDURE get_from_history_jobdef
1305 (
1306 p_sr_job_id IN NUMBER
1307 ,p_sr_report_date IN DATE
1308 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
1309 )
1310 IS
1311 l_proc varchar2(30) := 'get_from_history_jobdef';
1312 CURSOR JOBDEF_CUR IS
1313 SELECT SEGMENT1
1314 FROM PER_JOB_DEFINITIONS
1315 WHERE JOB_DEFINITION_ID =
1316 (SELECT JOB_DEFINITION_ID
1317 FROM PER_JOBS
1318 WHERE JOB_ID = g_job_id);
1319
1320 -- Declared record despite a "one column query" for future maintenance
1321 l_JOBDEF_REC JOBDEF_CUR%ROWTYPE;
1322 BEGIN
1323 hr_utility.set_location('Entering:'||l_proc,5);
1324
1325 OPEN JOBDEF_CUR;
1326
1327 FETCH JOBDEF_CUR INTO l_JOBDEF_REC;
1328
1329 IF JOBDEF_CUR%FOUND
1330 THEN
1331 p_sr_ghr_cpdf_temp.to_occ_code := l_JOBDEF_REC.SEGMENT1;
1332 END IF;
1333
1334 CLOSE JOBDEF_CUR;
1335
1336 END get_from_history_jobdef;
1337
1338 PROCEDURE get_from_history_dutsta
1339 (
1340 p_sr_location_id IN NUMBER
1341 ,p_sr_report_date IN DATE
1342 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
1343 )
1344 IS
1345 l_proc varchar2(30) := 'get_from_history_dutsta';
1346 l_log_text ghr_process_log.log_text%type;
1347 l_message_name ghr_process_log.message_name%type;
1348 l_log_date ghr_process_log.log_date%type;
1349
1350 CURSOR DUTSTACUR IS
1351 SELECT DUTY_STATION_CODE, DUTY_STATION_ID
1352 FROM GHR_DUTY_STATIONS_F
1353 WHERE trunc(p_sr_report_date) between effective_start_date and
1354 nvl(effective_end_date, p_sr_report_date)
1355 AND DUTY_STATION_ID =
1356 (SELECT LEI_INFORMATION3
1357 FROM HR_LOCATION_EXTRA_INFO
1358 WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
1359 AND LOCATION_ID = g_location_id);
1360
1361 -- Declared record despite a "one column query" for future maintenance
1362 l_DUTSTAREC DUTSTACUR%ROWTYPE;
1363
1364 -- JH EHRI Loc Address
1365 -- Bug#5508003 Added substr to all the address lines
1366 CURSOR cur_loc_address IS
1367 SELECT substr(address_line_1,1,35) address_line_1,
1368 substr(address_line_2,1,35) address_line_2,
1369 substr(address_line_3,1,35) address_line_3,
1370 substr(postal_code,1,35) postal_code,
1371 substr(town_or_city,1,35) town_or_city,
1372 substr(country,1,4) country,
1373 substr(region_2,1,2) region_2
1374 -- FROM hr_locations_v Bug 4863608 Performance
1375 FROM hr_locations_all
1376 WHERE location_id = g_location_id;
1377
1378 BEGIN
1379 hr_utility.set_location('Entering:'||l_proc,5);
1380 -- Begin Bug# 4753092
1381 g_message_name := 'Duty Station Details';
1382 -- End Bug# 4753092
1383 OPEN DUTSTACUR;
1384
1385 FETCH DUTSTACUR INTO l_DUTSTAREC;
1386
1387 IF DUTSTACUR%FOUND
1388 THEN
1389 p_sr_ghr_cpdf_temp.to_duty_station_code := l_DUTSTAREC.DUTY_STATION_CODE;
1390 g_duty_station_id := l_DUTSTAREC.DUTY_STATION_ID;
1391 END IF;
1392
1393 CLOSE DUTSTACUR;
1394 -- Begin Bug# 4753092
1395 g_message_name := 'Duty Station Location Address';
1396 -- End Bug# 4753092
1397 -- JH EHRI Loc Address
1398 --Bug# 4725292
1399 -- Commented this since the address will be fetched from Person Work address
1400 /*FOR cur_loc_addr_rec IN cur_loc_address LOOP
1401 p_sr_ghr_cpdf_temp.work_address_line1 := cur_loc_addr_rec.address_line_1;
1402 p_sr_ghr_cpdf_temp.work_address_line2 := cur_loc_addr_rec.address_line_2;
1403 p_sr_ghr_cpdf_temp.work_address_line3 := cur_loc_addr_rec.address_line_3;
1404 p_sr_ghr_cpdf_temp.work_address_line4 := NULL;
1405 p_sr_ghr_cpdf_temp.work_city := cur_loc_addr_rec.town_or_city;
1406 p_sr_ghr_cpdf_temp.work_postal_code := cur_loc_addr_rec.postal_code;
1407 p_sr_ghr_cpdf_temp.work_state_code := cur_loc_addr_rec.region_2;
1408 p_sr_ghr_cpdf_temp.work_country_code := cur_loc_addr_rec.country;
1409 END LOOP;*/
1410 --Bug# 4725292
1411 /* -- Commented this exception to return the cursor to calling program bug# 4753092
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 l_message_name := 'Unhandled Error';
1415 l_log_text := 'Unhandled Error under procedure get_from_history_dutsta'||
1416 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
1417 ghr_mto_int.log_message(p_procedure => l_message_name,
1418 p_message => l_log_text);
1419 COMMIT;
1420 */
1421 END get_from_history_dutsta;
1422 --Begin Bug# 4725292
1423 PROCEDURE get_from_per_wrkadd(
1424 p_sr_person_id IN NUMBER
1425 ,p_sr_report_date IN DATE
1426 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE
1427 ) IS
1428 l_proc varchar2(30) := 'get_from_per_wrkadd';
1429
1430 CURSOR workaddcur IS
1431 select address_line1,address_line2, address_line3, region_3 address_line4,
1432 COUNTRY, REGION_2, TOWN_OR_CITY CITY,POSTAL_CODE, REGION_1 County
1433 FROM PER_ADDRESSES
1434 Where Address_type= 'FED_WA'
1435 AND Person_id = p_sr_person_id
1436 And trunc(p_sr_report_date) between date_from and
1437 nvl(date_to, p_sr_report_date);
1438
1439 BEGIN
1440 hr_utility.set_location('Entering:'||l_proc,5);
1441 g_message_name := 'Person Work Address Details';
1442
1443 FOR cur_wrk_addr_rec IN workaddcur LOOP
1444 --Bug # 9817051 added substr in work address lines as it is coming as 240 from base table
1445 p_sr_ghr_cpdf_temp.work_address_line1 := SUBSTR(cur_wrk_addr_rec.address_line1,1,35);
1446 p_sr_ghr_cpdf_temp.work_address_line2 := SUBSTR(cur_wrk_addr_rec.address_line2,1,35);
1447 p_sr_ghr_cpdf_temp.work_address_line3 := SUBSTR(cur_wrk_addr_rec.address_line3,1,35);
1448 p_sr_ghr_cpdf_temp.work_address_line4 := SUBSTR(cur_wrk_addr_rec.address_line4,1,35);
1449 p_sr_ghr_cpdf_temp.work_city := cur_wrk_addr_rec.city;
1450 p_sr_ghr_cpdf_temp.work_postal_code := cur_wrk_addr_rec.postal_code;
1451
1452 IF cur_wrk_addr_rec.country <> 'US' THEN
1453 p_sr_ghr_cpdf_temp.work_region := cur_wrk_addr_rec.County;
1454 --Begin Bug# 6973541
1455 p_sr_ghr_cpdf_temp.work_country_code := cur_wrk_addr_rec.country;
1456 p_sr_ghr_cpdf_temp.work_state_code := NULL;
1457 --End Bug# 6973541
1458 ELSE
1459 p_sr_ghr_cpdf_temp.work_region := NULL;
1460 --Begin Bug# 6973541
1461 p_sr_ghr_cpdf_temp.work_country_code := NULL;
1462 p_sr_ghr_cpdf_temp.work_state_code := cur_wrk_addr_rec.region_2;
1463 --End Bug# 6973541
1464 END IF;
1465 END LOOP;
1466
1467 END get_from_per_wrkadd;
1468 --End Bug# 4725292
1469 PROCEDURE get_from_history_payele
1470 (p_sr_assignment_id IN NUMBER
1471 ,p_sr_report_date IN DATE
1472 ,p_sr_ghr_cpdf_temp IN OUT NOCOPY ghr_cpdf_temp%ROWTYPE)
1473 IS
1474 l_proc varchar2(30) := 'get_from_history_payele';
1475 l_scrn_ent_val_init PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
1476 l_scrn_ent_val PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
1477 l_value VARCHAR2(250);
1478 l_effective_start_date DATE:= NULL;
1479
1480
1481 --Bug # 4122470
1482 --Bug # 12958556 modified the cursor to consider the cancellation of base action
1483 cursor get_life_event_change(p_effective_date in date)
1484 is
1485 select rei_information2
1486 from ghr_pa_request_extra_info
1487 where information_type = 'GHR_US_PAR_FEGLI'
1488 and pa_request_id = (select max(pa_request_id) from ghr_pa_requests
1489 where pa_notification_id is not null
1490 start with pa_request_id = (select max(pa_request_id) from ghr_pa_requests A
1491 where pa_notification_id is not null
1492 and first_noa_code = '881'
1493 and employee_assignment_id = p_sr_assignment_id
1494 and effective_date = p_effective_date
1495 and not exists( select 1 from ghr_pa_requests
1496 where altered_pa_request_id = A.pa_request_id
1497 and pa_notification_id is not null
1498 and first_noa_code = '001'))
1499 connect by altered_pa_request_id = prior pa_request_id);
1500 --Bug # 4122470
1501
1502 BEGIN
1503 hr_utility.set_location('Entering:'||l_proc,5);
1504
1505 l_value := null;
1506 l_effective_start_date := null;
1507 -- Begin Bug# 4753092
1508 g_message_name := 'Fetch Element: Retirement Plan';
1509 -- End Bug# 4753092
1510 ghr_per_sum.get_element_details (p_element_name => 'Retirement Plan'
1511 ,p_input_value_name => 'Plan'
1512 ,p_assignment_id => p_sr_assignment_id
1513 ,p_effective_date => p_sr_report_date
1514 ,p_value => l_value
1515 ,p_effective_start_date => l_effective_start_date
1516 ,p_business_group_id => g_business_group_id);
1517
1518 p_sr_ghr_cpdf_temp.retirement_plan := l_value;
1519
1520 l_value := null;
1521 l_effective_start_date := null;
1522 -- Begin Bug# 4753092
1523 g_message_name := 'Fetch Element: FEGLI';
1524 -- End Bug# 4753092
1525 ghr_per_sum.get_element_details(p_element_name => 'FEGLI'
1526 ,p_input_value_name => 'FEGLI'
1527 ,p_assignment_id => p_sr_assignment_id
1528 ,p_effective_date => p_sr_report_date
1529 ,p_value => l_value
1530 ,p_effective_start_date => l_effective_start_date
1531 ,p_business_group_id => g_business_group_id);
1532
1533 p_sr_ghr_cpdf_temp.fegli := l_value;
1534 p_sr_ghr_cpdf_temp.fegli_elect_date := l_effective_start_date;
1535
1536 --Bug # 4122470
1537 for get_life_event_change_rec in get_life_event_change(p_effective_date => l_effective_start_date)
1538 loop
1539 p_sr_ghr_cpdf_temp.fegli_life_change_code := get_life_event_change_rec.rei_information2;
1540 end loop;
1541 --Bug # 4122470
1542
1543
1544
1545 l_value := null;
1546 l_effective_start_date := null;
1547 -- Begin Bug# 4753092
1548 g_message_name := 'Fetch Element: TSP Amount';
1549 -- End Bug# 4753092
1550 ghr_per_sum.get_element_details(p_element_name => 'TSP'
1551 ,p_input_value_name => 'Amount'
1552 ,p_assignment_id => p_sr_assignment_id
1553 ,p_effective_date => p_sr_report_date
1554 ,p_value => l_value
1555 ,p_effective_start_date => l_effective_start_date
1556 ,p_business_group_id => g_business_group_id);
1557
1558 IF l_value IS NOT NULL THEN
1559 p_sr_ghr_cpdf_temp.tsp_emp_amount := to_number(l_value);
1560 p_sr_ghr_cpdf_temp.tsp_effective_date := l_effective_start_date;
1561 END IF;
1562
1563 l_value := null;
1564 l_effective_start_date := null;
1565 -- Begin Bug# 4753092
1566 g_message_name := 'Fetch Element: TSP Rate';
1567 -- End Bug# 4753092
1568 ghr_per_sum.get_element_details (p_element_name => 'TSP'
1569 ,p_input_value_name => 'Rate'
1570 ,p_assignment_id => p_sr_assignment_id
1571 ,p_effective_date => p_sr_report_date
1572 ,p_value => l_value
1573 ,p_effective_start_date => l_effective_start_date
1574 ,p_business_group_id => g_business_group_id);
1575
1576 IF l_value IS NOT NULL THEN
1577 p_sr_ghr_cpdf_temp.tsp_elect_contrib_pct := to_number(l_value);
1578 p_sr_ghr_cpdf_temp.tsp_effective_date := l_effective_start_date;
1579 END IF;
1580
1581 l_value := null;
1582 l_effective_start_date := null;
1583 --Begin Bug# 4753092
1584 g_message_name := 'Fetch Element: TSP Elig Date';
1585 --End Bug# 4753092
1586 ghr_per_sum.get_element_details (p_element_name => 'TSP'
1587 ,p_input_value_name => 'Agncy Contrib Elig Date'
1588 ,p_assignment_id => p_sr_assignment_id
1589 ,p_effective_date => p_sr_report_date
1590 ,p_value => l_value
1591 ,p_effective_start_date => l_effective_start_date
1592 ,p_business_group_id => g_business_group_id);
1593
1594 p_sr_ghr_cpdf_temp.tsp_eligibility_date := fnd_date.canonical_to_date(l_value);
1595
1596
1597 l_value := null;
1598 l_effective_start_date := null;
1599 --Begin Bug# 4753092
1600 g_message_name := 'Fetch Element: HB Pre Tax plan';
1601 --End Bug# 4753092
1602 ghr_per_sum.get_element_details (p_element_name => 'Health Benefits Pre tax'
1603 ,p_input_value_name => 'Health Plan'
1604 ,p_assignment_id => p_sr_assignment_id
1605 ,p_effective_date => p_sr_report_date
1606 ,p_value => l_value
1607 ,p_effective_start_date => l_effective_start_date
1608 ,p_business_group_id => g_business_group_id);
1609
1610 p_sr_ghr_cpdf_temp.health_plan := l_value;
1611 -- BUG#6158983
1612 p_sr_ghr_cpdf_temp.fehb_elect_eff_date := l_effective_start_date;
1613 -- End BUG#6158983
1614
1615
1616 -- Report Plan + Enrollment as Health Plan.
1617 l_value := null;
1618 l_effective_start_date := null;
1619 --Begin Bug# 4753092
1620 g_message_name := 'Fetch Element: HB Pre tax Enrl';
1621 --End Bug# 4753092
1622 ghr_per_sum.get_element_details (p_element_name => 'Health Benefits Pre tax'
1623 ,p_input_value_name => 'Enrollment'
1624 ,p_assignment_id => p_sr_assignment_id
1625 ,p_effective_date => p_sr_report_date
1626 ,p_value => l_value
1627 ,p_effective_start_date => l_effective_start_date
1628 ,p_business_group_id => g_business_group_id);
1629
1630 IF l_value is NOT NULL THEN
1631 p_sr_ghr_cpdf_temp.health_plan := NVL(p_sr_ghr_cpdf_temp.health_plan, ' ') || l_value;
1632 END IF;
1633
1634 IF p_sr_ghr_cpdf_temp.health_plan is NULL THEN
1635 l_value := null;
1636 l_effective_start_date := null;
1637 --Begin Bug# 4753092
1638 g_message_name := ' Fetch Element: HB plan';
1639 --End Bug# 4753092
1640 ghr_per_sum.get_element_details (p_element_name => 'Health Benefits'
1641 ,p_input_value_name => 'Health Plan'
1642 ,p_assignment_id => p_sr_assignment_id
1643 ,p_effective_date => p_sr_report_date
1644 ,p_value => l_value
1645 ,p_effective_start_date => l_effective_start_date
1646 ,p_business_group_id => g_business_group_id);
1647
1648 p_sr_ghr_cpdf_temp.health_plan := l_value;
1649 -- BUG#6158983
1650 p_sr_ghr_cpdf_temp.fehb_elect_eff_date := l_effective_start_date;
1651 --End of BUG# 6158983
1652
1653 -- Report Plan + Enrollment as Health Plan.
1654 l_value := null;
1655 l_effective_start_date := null;
1656 --Begin Bug# 4753092
1657 g_message_name := ' Fetch Element: HB Enrollment';
1658 --End Bug# 4753092
1659 ghr_per_sum.get_element_details (p_element_name => 'Health Benefits'
1660 ,p_input_value_name => 'Enrollment'
1661 ,p_assignment_id => p_sr_assignment_id
1662 ,p_effective_date => p_sr_report_date
1663 ,p_value => l_value
1664 ,p_effective_start_date => l_effective_start_date
1665 ,p_business_group_id => g_business_group_id);
1666
1667 p_sr_ghr_cpdf_temp.health_plan := NVL(p_sr_ghr_cpdf_temp.health_plan, ' ') || l_value;
1668 END IF;
1669
1670 l_value := null;
1671 l_effective_start_date := null;
1672 --Begin Bug# 4753092
1673 g_message_name := 'Fetch Element: Total Pay';
1674 --End Bug# 4753092
1675 ghr_per_sum.get_element_details (p_element_name => 'Total Pay'
1676 ,p_input_value_name => 'Amount'
1677 ,p_assignment_id => p_sr_assignment_id
1678 ,p_effective_date => p_sr_report_date
1679 ,p_value => l_value
1680 ,p_effective_start_date => l_effective_start_date
1681 ,p_business_group_id => g_business_group_id);
1682
1683 p_sr_ghr_cpdf_temp.to_total_salary := to_number(l_value);
1684
1685 l_value := null;
1686 l_effective_start_date := null;
1687 --Begin Bug# 4753092
1688 g_message_name := 'Fetch Element: Basic Salary';
1689 --End Bug# 4753092
1690 ghr_per_sum.get_element_details (p_element_name => 'Basic Salary Rate'
1691 ,p_input_value_name => 'Rate'
1692 ,p_assignment_id => p_sr_assignment_id
1693 ,p_effective_date => p_sr_report_date
1694 ,p_value => l_value
1695 ,p_effective_start_date => l_effective_start_date
1696 ,p_business_group_id => g_business_group_id);
1697
1698 p_sr_ghr_cpdf_temp.to_basic_pay := to_number(l_value);
1699
1700 l_value := null;
1701 l_effective_start_date := null;
1702 --Begin Bug# 4753092
1703 g_message_name := 'Fetch Element: Adj Basic Pay';
1704 --End Bug# 4753092
1705 ghr_per_sum.get_element_details (p_element_name => 'Adjusted Basic Pay'
1706 ,p_input_value_name => 'Amount'
1707 ,p_assignment_id => p_sr_assignment_id
1708 ,p_effective_date => p_sr_report_date
1709 ,p_value => l_value
1710 ,p_effective_start_date => l_effective_start_date
1711 ,p_business_group_id => g_business_group_id);
1712
1713 p_sr_ghr_cpdf_temp.to_adj_basic_pay := to_number(l_value);
1714
1715 l_value := null;
1716 l_effective_start_date := null;
1717 --Begin Bug# 4753092
1718 g_message_name := 'Fetch Element: Locality Pay';
1719 --End Bug# 4753092
1720 -- FWFA Changes Bug#4444609
1721 ghr_per_sum.get_element_details (p_element_name => 'Locality Pay or SR Supplement'
1722 -- FWFA Changes Modify 'Locality Pay' to 'Locality Pay or SR Supplement'
1723 ,p_input_value_name => 'Rate'
1724 ,p_assignment_id => p_sr_assignment_id
1725 ,p_effective_date => p_sr_report_date
1726 ,p_value => l_value
1727 ,p_effective_start_date => l_effective_start_date
1728 ,p_business_group_id => g_business_group_id);
1729
1730 IF to_number(l_value) = 0 THEN
1731 p_sr_ghr_cpdf_temp.to_locality_adj := NULL;
1732 ELSE
1733 p_sr_ghr_cpdf_temp.to_locality_adj := to_number(l_value);
1734 END IF;
1735
1736
1737 l_value := null;
1738 l_effective_start_date := null;
1739 --Begin Bug# 4753092
1740 g_message_name := 'Fetch Element: Staffing Diff';
1741 --End Bug# 4753092
1742 ghr_per_sum.get_element_details (p_element_name => 'Staffing Differential'
1743 ,p_input_value_name => 'Amount'
1744 ,p_assignment_id => p_sr_assignment_id
1745 ,p_effective_date => p_sr_report_date
1746 ,p_value => l_value
1747 ,p_effective_start_date => l_effective_start_date
1748 ,p_business_group_id => g_business_group_id);
1749
1750 p_sr_ghr_cpdf_temp.to_staffing_differential := to_number(l_value);
1751
1752 l_value := null;
1753 l_effective_start_date := null;
1754 --Begin Bug# 4753092
1755 g_message_name := 'Fetch Element:Supervisory Diff';
1756 --End Bug# 4753092
1757 ghr_per_sum.get_element_details (p_element_name => 'Supervisory Differential'
1758 ,p_input_value_name => 'Amount'
1759 ,p_assignment_id => p_sr_assignment_id
1760 ,p_effective_date => p_sr_report_date
1761 ,p_value => l_value
1762 ,p_effective_start_date => l_effective_start_date
1763 ,p_business_group_id => g_business_group_id);
1764
1765 p_sr_ghr_cpdf_temp.to_supervisory_differential := to_number(l_value);
1766 l_value := null;
1767 l_effective_start_date := null;
1768 --Begin Bug# 4753092
1769 g_message_name := 'Fetch Element:Retention Allow';
1770 --End Bug# 4753092
1771 ghr_per_sum.get_element_details (p_element_name => 'Retention Allowance'
1772 ,p_input_value_name => 'Amount'
1773 ,p_assignment_id => p_sr_assignment_id
1774 ,p_effective_date => p_sr_report_date
1775 ,p_value => l_value
1776 ,p_effective_start_date => l_effective_start_date
1777 ,p_business_group_id => g_business_group_id);
1778
1779 p_sr_ghr_cpdf_temp.to_retention_allowance := to_number(l_value);
1780
1781 -- JH EHRI Placeholder for Benefits stuff setting to null for now per Rohini.
1782 /*
1783 -- Bug 4469808
1784 p_sr_ghr_cpdf_temp.alb_indicator := null;
1785 p_sr_ghr_cpdf_temp.alb_elect_date := null;
1786 p_sr_ghr_cpdf_temp.alb_notify_date := null;
1787 p_sr_ghr_cpdf_temp.cont_elect_date := null;
1788 p_sr_ghr_cpdf_temp.cont_notify_date := null;
1789
1790 */
1791
1792 --Bug 6158983
1793 /* p_sr_ghr_cpdf_temp.fegli_indicator := null;
1794 p_sr_ghr_cpdf_temp.fegli_elect_date := null; --Used this name for elect effective date.
1795 p_sr_ghr_cpdf_temp.fegli_notify_date := null;
1796 p_sr_ghr_cpdf_temp.fehb_indicator := null;
1797 p_sr_ghr_cpdf_temp.fehb_elect_date := null;
1798 p_sr_ghr_cpdf_temp.fehb_notify_date := null;
1799 p_sr_ghr_cpdf_temp.retire_indicator := null;
1800 p_sr_ghr_cpdf_temp.retire_elect_date := null;
1801 p_sr_ghr_cpdf_temp.retire_notify_date := null;
1802 p_sr_ghr_cpdf_temp.cont_term_elect_date := null;
1803 p_sr_ghr_cpdf_temp.cont_ins_pay_notify_date := null;
1804 p_sr_ghr_cpdf_temp.cont_pay_type_code := null;
1805 p_sr_ghr_cpdf_temp.fegli_life_change_code := null;
1806 p_sr_ghr_cpdf_temp.fegli_life_event_date := null;
1807 p_sr_ghr_cpdf_temp.fehb_event_code := null;*/
1808
1809 END get_from_history_payele;
1810
1811 PROCEDURE calc_is_foreign_duty_station
1812 ( p_report_date in date
1813 )
1814 IS
1815 l_proc varchar2(30) := 'calc_is_foreign_duty_station';
1816 l_log_text ghr_process_log.log_text%type;
1817 l_message_name ghr_process_log.message_name%type;
1818 l_log_date ghr_process_log.log_date%type;
1819
1820 CURSOR CALCDUTSTA_CUR IS
1821 SELECT STATE_OR_COUNTRY_CODE
1822 FROM GHR_DUTY_STATIONS_F
1823 WHERE trunc(p_report_date) between effective_start_date and
1824 nvl(effective_end_date, p_report_date)
1825 AND DUTY_STATION_ID =
1826 (SELECT LEI_INFORMATION3
1827 FROM HR_LOCATION_EXTRA_INFO
1828 WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
1829 AND LOCATION_ID = g_location_id);
1830
1831 l_STATE_CNTRY_CODE GHR_DUTY_STATIONS_F.STATE_OR_COUNTRY_CODE%TYPE;
1832
1833 BEGIN
1834 hr_utility.set_location('Entering:'||l_proc,5);
1835
1836 OPEN CALCDUTSTA_CUR;
1837
1838 FETCH CALCDUTSTA_CUR INTO l_STATE_CNTRY_CODE;
1839
1840 CLOSE CALCDUTSTA_CUR;
1841
1842 -- a determination of whether a duty station is foreign or domestic
1843 -- has been hard-coded. at the time this proc was written, there
1844 -- was talk that a future release of GHR would represent this value
1845 -- as a flexfield (i.e., no need to calculate). furthermore,
1846 -- the ghr_cpdf_temp.from_duty_station_code will be used as
1847 -- a temporary storage for this value
1848
1849
1850 IF (l_STATE_CNTRY_CODE >= '01' AND
1851 l_STATE_CNTRY_CODE <= '99' ) OR
1852 l_STATE_CNTRY_CODE IN ('GQ','RQ','AQ','FM','JQ',
1853 'CQ','MQ','RM','HQ','PS',
1854 'BQ','WQ','VQ')
1855 THEN
1856 g_ghr_cpdf_temp.from_duty_station_code := 'N';
1857 ELSE
1858 g_ghr_cpdf_temp.from_duty_station_code := 'Y';
1859 END IF;
1860
1861 END calc_is_foreign_duty_station;
1862
1863 PROCEDURE insert_row
1864 IS
1865 l_proc varchar2(30) := 'insert_row';
1866 l_log_text ghr_process_log.log_text%type;
1867 l_message_name ghr_process_log.message_name%type;
1868
1869 BEGIN
1870 hr_utility.set_location('Entering:'||l_proc,5);
1871
1872 -- Bug#3231946 Added reference to parameters as the function definition is changed
1873 IF NOT (ghr_cpdf_dynrpt.get_loc_pay_area_code(p_duty_station_id => g_duty_station_id,
1874 p_effective_date => g_ghr_cpdf_temp.effective_date) <> '99')
1875 THEN
1876 g_ghr_cpdf_temp.from_locality_adj := NULL;
1877 g_ghr_cpdf_temp.to_locality_adj := NULL;
1878 ELSIF ghr_cpdf_dynrpt.get_equivalent_pay_plan(
1879 NVL(g_ghr_cpdf_temp.retained_pay_plan, g_ghr_cpdf_temp.to_pay_plan)) = 'FW'
1880 THEN
1881 g_ghr_cpdf_temp.from_locality_adj := NULL;
1882 g_ghr_cpdf_temp.to_locality_adj := NULL;
1883 END IF;
1884
1885 hr_utility.set_location('Inside insert row Locality adj' || g_ghr_cpdf_temp.to_locality_adj,111 );
1886
1887
1888 INSERT INTO ghr_cpdf_temp (
1889 report_type
1890 ,session_id
1891 ,academic_discipline
1892 ,agency_code
1893 ,annuitant_indicator
1894 ,award_amount
1895 ,bargaining_unit_status
1896 ,benefit_amount
1897 ,citizenship
1898 ,creditable_military_service
1899 ,current_appointment_auth1
1900 ,current_appointment_auth2
1901 ,to_duty_station_code
1902 ,education_level
1903 ,effective_date
1904 ,employee_date_of_birth
1905 ,employee_first_name
1906 ,employee_last_name
1907 ,employee_middle_names
1908 ,from_national_identifier
1909 ,fegli
1910 ,fers_coverage
1911 ,first_action_la_code1
1912 ,first_action_la_code2
1913 ,first_noa_code
1914 ,flsa_category
1915 ,from_basic_pay
1916 ,from_duty_station_code
1917 ,from_grade_or_level
1918 ,from_locality_adj
1919 ,from_occ_code
1920 ,from_pay_table_id
1921 ,from_pay_basis
1922 ,from_pay_plan
1923 ,from_pay_rate_determinant
1924 ,from_retirement_coverage
1925 ,from_step_or_rate
1926 ,from_total_salary
1927 ,from_work_schedule
1928 ,frozen_service
1929 ,functional_class
1930 ,handicap_code
1931 ,health_plan
1932 ,individual_group_award
1933 ,organizational_component
1934 ,pay_status
1935 ,personnel_office_id
1936 ,position_occupied
1937 ,race_national_origin
1938 ,rating_of_record
1939 ,rating_of_record_level
1940 ,rating_of_record_pattern
1941 ,rating_of_record_period_starts
1942 ,rating_of_record_period_ends
1943 ,retained_grade_or_level
1944 ,retained_pay_plan
1945 ,retained_step_or_rate
1946 ,retirement_plan
1947 ,second_noa_code
1948 ,service_comp_date
1949 ,sex
1950 ,supervisory_status
1951 ,tenure
1952 ,to_basic_pay
1953 ,to_grade_or_level
1954 ,to_locality_adj
1955 ,to_national_identifier
1956 ,to_occ_code
1957 ,to_pay_basis
1958 ,to_pay_plan
1959 ,to_pay_rate_determinant
1960 ,to_pay_table_id
1961 ,to_retention_allowance
1962 ,to_staffing_differential
1963 ,to_step_or_rate
1964 ,to_supervisory_differential
1965 ,to_total_salary
1966 ,to_work_schedule
1967 ,veterans_preference
1968 ,veterans_status
1969 ,year_degree_attained,
1970 name_title,
1971 position_title,
1972 award_dollars,
1973 award_hours,
1974 award_percentage,
1975 SCD_retirement,
1976 SCD_rif,
1977 -- New EHRI Starts Here ---
1978 ehri_employee_id,
1979 agency_employee_id,
1980 world_citizenship,
1981 slct_serv_regi_indicator,
1982 svc_oblig_type_code1,
1983 svc_oblig_type_end_date1,
1984 svc_oblig_type_code2,
1985 svc_oblig_type_end_date2,
1986 svc_oblig_type_code3,
1987 svc_oblig_type_end_date3,
1988 svc_oblig_type_code4,
1989 svc_oblig_type_end_date4,
1990 appoint_type_code,
1991 part_time_hours,
1992 to_adj_basic_pay,
1993 spcl_pay_tbl_type,
1994 act_svc_indicator,
1995 appropriation_code,
1996 comp_pos_indicator,
1997 mil_char_svc_code,
1998 mil_svc_sno,
1999 mil_svc_start_date,
2000 mil_svc_end_date,
2001 mil_branch_code,
2002 mil_discharge_code,
2003 career_tenure_code,
2004 fegli_life_change_code,
2005 fegli_life_event_date,
2006 fegli_elect_date,
2007 fehb_event_code,
2008 tsp_eligibility_date,
2009 tsp_effective_date,
2010 tsp_elect_contrib_pct,
2011 tsp_emp_amount,
2012 fers_elect_date,
2013 fers_elect_indicator,
2014 alb_indicator,
2015 alb_elect_date,
2016 alb_notify_date,
2017 fegli_indicator,
2018 --fegli_elect_date,
2019 fegli_notify_date,
2020 fehb_indicator,
2021 fehb_elect_date,
2022 --bug# 6158983
2023 fehb_elect_eff_date,
2024 appointment_nte_date,
2025 --6158983
2026 fehb_notify_date,
2027 retire_indicator,
2028 retire_elect_date,
2029 retire_notify_date,
2030 cont_elect_date,
2031 cont_notify_date,
2032 cont_term_elect_date,
2033 cont_ins_pay_notify_date,
2034 cont_pay_type_code,
2035 scd_ses,
2036 scd_spcl_retire,
2037 leave_scd,
2038 tsp_scd,
2039 disability_retire_notify,
2040 work_address_line1,
2041 work_address_line2,
2042 work_address_line3,
2043 work_address_line4,
2044 work_city,
2045 work_region, --Bug# 4725292
2046 work_state_code,
2047 work_postal_code,
2048 work_country_code,
2049 work_employee_email,
2050 work_phone_number,
2051 home_phone_number,
2052 cell_phone_number,
2053 emrgncy_cntct_family_name1,
2054 emrgncy_cntct_given_name1,
2055 emrgncy_cntct_middle_name1,
2056 emrgncy_cntct_suffix1,
2057 emrgncy_cntct_infrm_upd_dt1,
2058 emrgncy_cntct_phone1,
2059 emrgncy_cntct_family_name2,
2060 emrgncy_cntct_given_name2,
2061 emrgncy_cntct_middle_name2,
2062 emrgncy_cntct_suffix2,
2063 emrgncy_cntct_infrm_upd_dt2,
2064 emrgncy_cntct_phone2,
2065 language_code1,
2066 lang_prof_type1,
2067 lang_prof_level1,
2068 language_code2,
2069 lang_prof_type2,
2070 lang_prof_level2,
2071 language_code3,
2072 lang_prof_type3,
2073 lang_prof_level3,
2074 language_code4,
2075 lang_prof_type4,
2076 lang_prof_level4,
2077 language_code5,
2078 lang_prof_type5,
2079 lang_prof_level5,
2080 language_code6,
2081 lang_prof_type6,
2082 lang_prof_level6,
2083 language_code7,
2084 lang_prof_type7,
2085 lang_prof_level7,
2086 language_code8,
2087 lang_prof_type8,
2088 lang_prof_level8,
2089 spcl_salary_rate,
2090 race_ethnic_info,
2091 to_spl_rate_supplement,
2092 Telework_Indicator --Bug# 14245991
2093 -- created_by,
2094 -- creation_Date,
2095 -- last_updated_by,
2096 -- last_update_date,
2097 -- last_update_login
2098 )
2099 values (
2100 'STATUS'
2101 ,userenv('SESSIONID')
2102 ,g_ghr_cpdf_temp.academic_discipline
2103 ,g_ghr_cpdf_temp.agency_code
2104 ,g_ghr_cpdf_temp.annuitant_indicator
2105 ,g_ghr_cpdf_temp.award_amount
2106 ,g_ghr_cpdf_temp.bargaining_unit_status
2107 ,g_ghr_cpdf_temp.benefit_amount
2108 ,g_ghr_cpdf_temp.citizenship
2109 ,g_ghr_cpdf_temp.creditable_military_service
2110 ,g_ghr_cpdf_temp.current_appointment_auth1
2111 ,g_ghr_cpdf_temp.current_appointment_auth2
2112 ,g_ghr_cpdf_temp.to_duty_station_code
2113 ,g_ghr_cpdf_temp.education_level
2114 ,g_ghr_cpdf_temp.effective_date
2115 ,g_ghr_cpdf_temp.employee_date_of_birth
2116 ,g_ghr_cpdf_temp.employee_first_name
2117 ,g_ghr_cpdf_temp.employee_last_name
2118 ,g_ghr_cpdf_temp.employee_middle_names
2119 ,g_ghr_cpdf_temp.from_national_identifier
2120 ,g_ghr_cpdf_temp.fegli
2121 ,g_ghr_cpdf_temp.fers_coverage
2122 ,g_ghr_cpdf_temp.first_action_la_code1
2123 ,g_ghr_cpdf_temp.first_action_la_code2
2124 ,g_ghr_cpdf_temp.first_noa_code
2125 ,g_ghr_cpdf_temp.flsa_category
2126 ,g_ghr_cpdf_temp.from_basic_pay
2127 ,g_ghr_cpdf_temp.from_duty_station_code
2128 ,g_ghr_cpdf_temp.from_grade_or_level
2129 ,g_ghr_cpdf_temp.from_locality_adj
2130 ,g_ghr_cpdf_temp.from_occ_code
2131 ,g_ghr_cpdf_temp.from_pay_table_id
2132 ,g_ghr_cpdf_temp.from_pay_basis
2133 ,g_ghr_cpdf_temp.from_pay_plan
2134 ,g_ghr_cpdf_temp.from_pay_rate_determinant
2135 ,g_ghr_cpdf_temp.from_retirement_coverage
2136 ,g_ghr_cpdf_temp.from_step_or_rate
2137 ,g_ghr_cpdf_temp.from_total_salary
2138 ,g_ghr_cpdf_temp.from_work_schedule
2139 ,g_ghr_cpdf_temp.frozen_service
2140 ,g_ghr_cpdf_temp.functional_class
2141 ,g_ghr_cpdf_temp.handicap_code
2142 ,g_ghr_cpdf_temp.health_plan
2143 ,g_ghr_cpdf_temp.individual_group_award
2144 ,g_ghr_cpdf_temp.organizational_component
2145 ,g_ghr_cpdf_temp.pay_status
2146 ,g_ghr_cpdf_temp.personnel_office_id
2147 ,g_ghr_cpdf_temp.position_occupied
2148 ,g_ghr_cpdf_temp.race_national_origin
2149 ,g_ghr_cpdf_temp.rating_of_record
2150 ,g_ghr_cpdf_temp.rating_of_record_level
2151 ,g_ghr_cpdf_temp.rating_of_record_pattern
2152 ,g_ghr_cpdf_temp.rating_of_record_period_starts
2153 ,g_ghr_cpdf_temp.rating_of_record_period_ends
2154 ,g_ghr_cpdf_temp.retained_grade_or_level
2155 ,g_ghr_cpdf_temp.retained_pay_plan
2156 ,g_ghr_cpdf_temp.retained_step_or_rate
2157 ,g_ghr_cpdf_temp.retirement_plan
2158 ,g_ghr_cpdf_temp.second_noa_code
2159 ,g_ghr_cpdf_temp.service_comp_date
2160 ,g_ghr_cpdf_temp.sex
2161 ,g_ghr_cpdf_temp.supervisory_status
2162 ,g_ghr_cpdf_temp.tenure
2163 ,g_ghr_cpdf_temp.to_basic_pay
2164 ,g_ghr_cpdf_temp.to_grade_or_level
2165 ,g_ghr_cpdf_temp.to_locality_adj
2166 ,g_ghr_cpdf_temp.to_national_identifier
2167 ,g_ghr_cpdf_temp.to_occ_code
2168 ,g_ghr_cpdf_temp.to_pay_basis
2169 ,g_ghr_cpdf_temp.to_pay_plan
2170 ,g_ghr_cpdf_temp.to_pay_rate_determinant
2171 ,g_ghr_cpdf_temp.to_pay_table_id
2172 ,g_ghr_cpdf_temp.to_retention_allowance
2173 ,g_ghr_cpdf_temp.to_staffing_differential
2174 ,g_ghr_cpdf_temp.to_step_or_rate
2175 ,g_ghr_cpdf_temp.to_supervisory_differential
2176 ,g_ghr_cpdf_temp.to_total_salary
2177 ,g_ghr_cpdf_temp.to_work_schedule
2178 ,g_ghr_cpdf_temp.veterans_preference
2179 ,g_ghr_cpdf_temp.veterans_status
2180 ,g_ghr_cpdf_temp.year_degree_attained,
2181 -- p_ghr_cpdf_temp_rec.employee_first_name,
2182 -- p_ghr_cpdf_temp_rec.employee_middle_names,
2183 g_ghr_cpdf_temp.name_title,
2184 g_ghr_cpdf_temp.position_title,
2185 g_ghr_cpdf_temp.award_dollars,
2186 g_ghr_cpdf_temp.award_hours,
2187 g_ghr_cpdf_temp.award_percentage,
2188 g_ghr_cpdf_temp.SCD_retirement,
2189 g_ghr_cpdf_temp.SCD_rif,
2190 -- JH NEW EHRI
2191 g_ghr_cpdf_temp.ehri_employee_id,
2192 g_ghr_cpdf_temp.agency_employee_id,
2193 g_ghr_cpdf_temp.world_citizenship,
2194 g_ghr_cpdf_temp.slct_serv_regi_indicator,
2195 g_ghr_cpdf_temp.svc_oblig_type_code1,
2196 g_ghr_cpdf_temp.svc_oblig_type_end_date1,
2197 g_ghr_cpdf_temp.svc_oblig_type_code2,
2198 g_ghr_cpdf_temp.svc_oblig_type_end_date2,
2199 g_ghr_cpdf_temp.svc_oblig_type_code3,
2200 g_ghr_cpdf_temp.svc_oblig_type_end_date3,
2201 g_ghr_cpdf_temp.svc_oblig_type_code4,
2202 g_ghr_cpdf_temp.svc_oblig_type_end_date4,
2203 g_ghr_cpdf_temp.appoint_type_code,
2204 g_ghr_cpdf_temp.part_time_hours,
2205 g_ghr_cpdf_temp.to_adj_basic_pay,
2206 g_ghr_cpdf_temp.spcl_pay_tbl_type,
2207 g_ghr_cpdf_temp.act_svc_indicator,
2208 g_ghr_cpdf_temp.appropriation_code,
2209 g_ghr_cpdf_temp.comp_pos_indicator,
2210 g_ghr_cpdf_temp.mil_char_svc_code,
2211 g_ghr_cpdf_temp.mil_svc_sno,
2212 g_ghr_cpdf_temp.mil_svc_start_date,
2213 g_ghr_cpdf_temp.mil_svc_end_date,
2214 g_ghr_cpdf_temp.mil_branch_code,
2215 g_ghr_cpdf_temp.mil_discharge_code,
2216 g_ghr_cpdf_temp.career_tenure_code,
2217 g_ghr_cpdf_temp.fegli_life_change_code,
2218 g_ghr_cpdf_temp.fegli_life_event_date,
2219 g_ghr_cpdf_temp.fegli_elect_date,
2220 g_ghr_cpdf_temp.fehb_event_code,
2221 g_ghr_cpdf_temp.tsp_eligibility_date,
2222 g_ghr_cpdf_temp.tsp_effective_date,
2223 g_ghr_cpdf_temp.tsp_elect_contrib_pct,
2224 g_ghr_cpdf_temp.tsp_emp_amount,
2225 g_ghr_cpdf_temp.fers_elect_date,
2226 g_ghr_cpdf_temp.fers_elect_indicator,
2227 g_ghr_cpdf_temp.alb_indicator,
2228 g_ghr_cpdf_temp.alb_elect_date,
2229 g_ghr_cpdf_temp.alb_notify_date,
2230 g_ghr_cpdf_temp.fegli_indicator,
2231 --g_ghr_cpdf_temp.fegli_elect_date,
2232 g_ghr_cpdf_temp.fegli_notify_date,
2233 g_ghr_cpdf_temp.fehb_indicator,
2234 g_ghr_cpdf_temp.fehb_elect_date,
2235 --Bug# 6158983
2236 g_ghr_cpdf_temp.fehb_elect_eff_date,
2237 g_ghr_cpdf_temp.appointment_nte_date,
2238 --Bug# 6158983
2239 g_ghr_cpdf_temp.fehb_notify_date,
2240 g_ghr_cpdf_temp.retire_indicator,
2241 g_ghr_cpdf_temp.retire_elect_date,
2242 g_ghr_cpdf_temp.retire_notify_date,
2243 g_ghr_cpdf_temp.cont_elect_date,
2244 g_ghr_cpdf_temp.cont_notify_date,
2245 g_ghr_cpdf_temp.cont_term_elect_date,
2246 g_ghr_cpdf_temp.cont_ins_pay_notify_date,
2247 g_ghr_cpdf_temp.cont_pay_type_code,
2248 g_ghr_cpdf_temp.scd_ses,
2249 g_ghr_cpdf_temp.scd_spcl_retire,
2250 g_ghr_cpdf_temp.leave_scd,
2251 g_ghr_cpdf_temp.tsp_scd,
2252 g_ghr_cpdf_temp.disability_retire_notify,
2253 g_ghr_cpdf_temp.work_address_line1,
2254 g_ghr_cpdf_temp.work_address_line2,
2255 g_ghr_cpdf_temp.work_address_line3,
2256 g_ghr_cpdf_temp.work_address_line4,
2257 g_ghr_cpdf_temp.work_city,
2258 g_ghr_cpdf_temp.work_region, --Bug# 4725292
2259 g_ghr_cpdf_temp.work_state_code,
2260 g_ghr_cpdf_temp.work_postal_code,
2261 g_ghr_cpdf_temp.work_country_code,
2262 g_ghr_cpdf_temp.work_employee_email,
2263 g_ghr_cpdf_temp.work_phone_number,
2264 g_ghr_cpdf_temp.home_phone_number,
2265 g_ghr_cpdf_temp.cell_phone_number,
2266 g_ghr_cpdf_temp.emrgncy_cntct_family_name1,
2267 g_ghr_cpdf_temp.emrgncy_cntct_given_name1,
2268 g_ghr_cpdf_temp.emrgncy_cntct_middle_name1,
2269 g_ghr_cpdf_temp.emrgncy_cntct_suffix1,
2270 g_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt1,
2271 g_ghr_cpdf_temp.emrgncy_cntct_phone1,
2272 g_ghr_cpdf_temp.emrgncy_cntct_family_name2,
2273 g_ghr_cpdf_temp.emrgncy_cntct_given_name2,
2274 g_ghr_cpdf_temp.emrgncy_cntct_middle_name2,
2275 g_ghr_cpdf_temp.emrgncy_cntct_suffix2,
2276 g_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt2,
2277 g_ghr_cpdf_temp.emrgncy_cntct_phone2,
2278 g_ghr_cpdf_temp.language_code1,
2279 g_ghr_cpdf_temp.lang_prof_type1,
2280 g_ghr_cpdf_temp.lang_prof_level1,
2281 g_ghr_cpdf_temp.language_code2,
2282 g_ghr_cpdf_temp.lang_prof_type2,
2283 g_ghr_cpdf_temp.lang_prof_level2,
2284 g_ghr_cpdf_temp.language_code3,
2285 g_ghr_cpdf_temp.lang_prof_type3,
2286 g_ghr_cpdf_temp.lang_prof_level3,
2287 g_ghr_cpdf_temp.language_code4,
2288 g_ghr_cpdf_temp.lang_prof_type4,
2289 g_ghr_cpdf_temp.lang_prof_level4,
2290 g_ghr_cpdf_temp.language_code5,
2291 g_ghr_cpdf_temp.lang_prof_type5,
2292 g_ghr_cpdf_temp.lang_prof_level5,
2293 g_ghr_cpdf_temp.language_code6,
2294 g_ghr_cpdf_temp.lang_prof_type6,
2295 g_ghr_cpdf_temp.lang_prof_level6,
2296 g_ghr_cpdf_temp.language_code7,
2297 g_ghr_cpdf_temp.lang_prof_type7,
2298 g_ghr_cpdf_temp.lang_prof_level7,
2299 g_ghr_cpdf_temp.language_code8,
2300 g_ghr_cpdf_temp.lang_prof_type8,
2301 g_ghr_cpdf_temp.lang_prof_level8,
2302 g_ghr_cpdf_temp.spcl_salary_rate,
2303 g_ghr_cpdf_temp.race_ethnic_info,
2304 g_ghr_cpdf_temp.to_spl_rate_supplement,
2305 g_ghr_cpdf_temp.Telework_Indicator --bug# 14245991
2306 );
2307
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310 l_message_name := 'Unhandled Error';
2311 l_log_text := 'Unhandled Error under procedure insert_row'||
2312 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
2313 ghr_mto_int.log_message(p_procedure => l_message_name,
2314 p_message => l_log_text);
2315 --dbms_output.put_line(l_log_text);
2316 COMMIT;
2317
2318 END insert_row;
2319
2320 PROCEDURE purge_suppression
2321 IS
2322 l_proc varchar2(30) := 'purge_suppression';
2323 BEGIN
2324 hr_utility.set_location('Entering:'||l_proc,5);
2325
2326 -- bug 743502 moved the checking of agency code matching the parameter passed in to
2327 -- to as soon as possible, not here at the end!
2328 -- Bug#5225838 Modified citizenship <> '1' to citizenship <>'Y'
2329 DELETE FROM ghr_cpdf_temp
2330 WHERE (report_type='STATUS')
2331 AND (
2332 -- *** SUPPRESS NON APPROPRIATED EMPLOYEES / COMMISSIONED OFFICERS
2333 ( to_pay_plan IN ('NA','NL','NS','CC') )
2334 -- *** EXCLUDE NON US CITIZENS WORKING IN FOREIGN DUTY STATIONS
2335 OR ( from_duty_station_code = 'Y'
2336 AND decode(citizenship, NULL, ' ', citizenship) <> 'Y' )
2337 -- *** EXCLUDE CERTAIN AGENCIES
2338 OR ( agency_code IN ('CI00','DD05','DD28','FR00',
2339 'PO00','PJ00','TV00','WH01') )
2340 -- *** EXCLUDE CERTAIN SUBELEMENTS
2341 OR ( substr(agency_code,1,2) IN ('LL','LB','LA','LD','LG','LC') )
2342 -- *** EXCLUDE NON SELECTED AGENCY CODE
2343 -- OR ( decode(agency_code, NULL, ' ', agency_code)
2344 -- not like DECODE(g_agency,NULL,'%',rtrim(g_agency)||'%'))
2345 )
2346 ;
2347
2348 END purge_suppression;
2349
2350 PROCEDURE get_suffix_lname(p_last_name in varchar2,
2351 p_report_date in date,
2352 p_suffix out nocopy varchar2,
2353 p_lname out nocopy varchar2)
2354 IS
2355 l_suffix_pos number;
2356 l_total_len number;
2357 l_proc varchar2(30) := 'get_suffix_lname';
2358
2359
2360 CURSOR GET_SUFFIX IS
2361 SELECT INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
2362 LENGTH(p_last_name)
2363 FROM HR_LOOKUPS
2364 WHERE LOOKUP_TYPE = 'GHR_US_NAME_SUFFIX'
2365 AND TRUNC(p_report_date) BETWEEN NVL(START_DATE_ACTIVE,p_report_date)
2366 AND NVL(END_DATE_ACTIVE,p_report_date)
2367 AND RTRIM(SUBSTR(TRANSLATE(UPPER(p_last_name),',.',' '),
2368 INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
2369 LENGTH(p_last_name)),' ') = ' '||UPPER(LOOKUP_CODE)
2370 AND ROWNUM = 1;
2371 BEGIN
2372
2373 hr_utility.set_location('Entering:'||l_proc,5);
2374
2375 IF GET_SUFFIX%ISOPEN THEN
2376 CLOSE GET_SUFFIX;
2377 END IF;
2378
2379 OPEN GET_SUFFIX;
2380 --getting the position of a suffix appended in the lastname by comparing the lastname
2381 -- with the suffixes available in lookup*/
2382 FETCH GET_SUFFIX INTO l_suffix_pos, l_total_len;
2383 IF GET_SUFFIX%NOTFOUND OR l_suffix_pos is NULL THEN
2384 p_lname := RTRIM(p_last_name,' ,.');
2385 p_suffix := NULL;
2386 ELSE
2387 p_lname := RTRIM(SUBSTR(p_last_name, 0, l_suffix_pos-1),' ,.');
2388 p_suffix := SUBSTR(p_last_name,l_suffix_pos+1,l_total_len);
2389 END IF;
2390 CLOSE GET_SUFFIX;
2391 END get_suffix_lname;
2392
2393
2394
2395 ---------------------------------------------------------------------------
2396 --- THIS IS PROC TO GENERATE THE ASCII and XML file
2397 ---------------------------------------------------------------------------
2398 --
2399 PROCEDURE WritetoFile (p_input_file_name VARCHAR2
2400 ,p_gen_xml_file IN VARCHAR2
2401 ,p_gen_txt_file IN VARCHAR2)
2402 IS
2403 p_xml_fp UTL_FILE.FILE_TYPE;
2404 p_ascii_fp UTL_FILE.FILE_TYPE;
2405 l_audit_log_dir varchar2(500);
2406 l_xml_file_name varchar2(500);
2407 l_ascii_file_name varchar2(500);
2408 l_output_xml_fname varchar2(500);
2409 l_output_ascii_fname varchar2(500);
2410 v_tags t_tags;
2411 l_count NUMBER;
2412 l_session_id NUMBER;
2413 l_request_id NUMBER;
2414 l_temp VARCHAR2(500);
2415
2416 CURSOR c_cpdf_status(c_session_id NUMBER) IS
2417 SELECT *
2418 FROM GHR_CPDF_TEMP
2419 WHERE SESSION_ID = c_session_id
2420 AND report_type = 'STATUS';
2421
2422 --
2423 /*
2424 CURSOR c_out_dir(c_request_id fnd_concurrent_requests.request_id%type) IS
2425 SELECT outfile_name
2426 FROM FND_CONCURRENT_REQUESTS
2427 WHERE request_id = c_request_id;
2428 */
2429 --
2430 BEGIN
2431 -- Assigning the File name.
2432 l_xml_file_name := p_input_file_name || '.xml';
2433 l_ascii_file_name := p_input_file_name || '.txt';
2434 l_count := 1;
2435 l_session_id := USERENV('SESSIONID');
2436
2437 /*
2438 l_request_id := fnd_profile.VALUE('CONC_REQUEST_ID');
2439 FOR l_out_dir IN c_out_dir(l_request_id) LOOP
2440 l_temp := l_out_dir.outfile_name;
2441 END LOOP;
2442 l_audit_log_dir := SUBSTR(l_temp,1,INSTR(l_temp,'o'||l_request_id)-1);
2443 */
2444 --
2445 select value
2446 into l_audit_log_dir
2447 from v$parameter
2448 where name = 'utl_file_dir';
2449 -- Check whether more than one util file directory is found
2450 IF INSTR(l_audit_log_dir,',') > 0 THEN
2451 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
2452 END IF;
2453
2454 -- JH Display Output File Directory
2455 --dbms_output.put_line('Output File Directory '||l_audit_log_dir);
2456
2457 -- Find out whether the OS is MS or Unix/Linux based
2458 -- If it's greater than 0, it's Unix/Linux based environment
2459 IF INSTR(l_audit_log_dir,'/') > 0 THEN
2460 l_output_xml_fname := l_audit_log_dir || '/' || l_xml_file_name;
2461 l_output_ascii_fname := l_audit_log_dir || '/' || l_ascii_file_name;
2462 ELSE
2463 l_output_xml_fname := l_audit_log_dir || '\' || l_xml_file_name;
2464 l_output_ascii_fname := l_audit_log_dir || '\' || l_ascii_file_name;
2465 END IF;
2466
2467
2468 -- fnd_file.put_line(fnd_file.log,'-----'||l_audit_log_dir);
2469 -- Bug 5013892
2470 -- p_xml_fp := utl_file.fopen(l_audit_log_dir,l_xml_file_name,'w');
2471 -- p_ascii_fp := utl_file.fopen(l_audit_log_dir,l_ascii_file_name,'w');
2472 p_ascii_fp := utl_file.fopen(l_audit_log_dir,l_ascii_file_name,'w',32767);
2473
2474 IF p_gen_xml_file = 'Y' THEN
2475 p_xml_fp := utl_file.fopen(l_audit_log_dir,l_xml_file_name,'w',32767);
2476 -- End Bug 5013892
2477 utl_file.put_line(p_xml_fp,'<?xml version="1.0" encoding="UTF-8"?>');
2478 -- Writing from and to dates
2479 utl_file.put_line(p_xml_fp,'<Records>');
2480 -- Loop through cursor and write the values into the XML and ASCII File.
2481 FOR ctr_table IN c_cpdf_status(l_session_id) LOOP
2482 WriteTagValues(ctr_table,v_tags);
2483 utl_file.put_line(p_xml_fp,'<Record' || l_count || '>');
2484 WriteXMLvalues(p_xml_fp,v_tags);
2485 utl_file.put_line(p_xml_fp,'</Record' || l_count || '>');
2486 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_txt_file);
2487 l_count := l_count + 1;
2488 END LOOP;
2489 utl_file.put_line(p_xml_fp,'</Records>');
2490 utl_file.fclose(p_xml_fp);
2491 ELSE
2492 FOR ctr_table IN c_cpdf_status(l_session_id) LOOP
2493 WriteTagValues(ctr_table,v_tags);
2494 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_txt_file);
2495 l_count := l_count + 1;
2496 END LOOP;
2497 END IF;
2498
2499 l_count := l_count - 1;
2500 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
2501 fnd_file.put_line(fnd_file.log,'Total Records : ' || l_count );
2502 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
2503 -- Write the end tag and close the XML File.
2504
2505 IF p_gen_xml_file = 'Y' OR p_gen_txt_file = 'Y' THEN
2506 fnd_file.put_line(fnd_file.log,'------------Path of output file----------------');
2507 IF p_gen_xml_file = 'Y' THEN
2508 fnd_file.put_line(fnd_file.log,'XML file : ' || l_output_xml_fname);
2509 END IF;
2510 IF p_gen_txt_file = 'Y' THEN
2511 fnd_file.put_line(fnd_file.log,'Text file : ' || l_output_ascii_fname);
2512 END IF;
2513 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
2514 END IF;
2515 END WritetoFile;
2516
2517 ---------------------------------------------------------------------------------------------
2518 -- This Procedure writes one record from the temporary table GHR_CPDF_TEMP
2519 -- to a PL/SQL table p_tags at a time. This PL/SQL table p_tags is used to write to file.
2520 ---------------------------------------------------------------------------------------------
2521
2522 PROCEDURE WriteTagValues(p_cpdf_status GHR_CPDF_TEMP%rowtype,p_tags OUT NOCOPY t_tags)
2523 IS
2524 l_count NUMBER;
2525 BEGIN
2526 l_count := 1;
2527 -- Writing to Tags
2528 p_tags(l_count).tagname := 'Social_Security_Number';
2529 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.to_national_identifier,1,3) || '-' ||SUBSTR(p_cpdf_status.to_national_identifier,4,2) || '-' ||SUBSTR(p_cpdf_status.to_national_identifier,6) ;
2530 l_count := l_count+1;
2531
2532 p_tags(l_count).tagname := 'Birth_Date';
2533 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.employee_date_of_birth,'YYYY-MM-DD');
2534 l_count := l_count+1;
2535
2536 -- Check this
2537 p_tags(l_count).tagname := 'EHRI_Employee_ID';
2538 p_tags(l_count).tagvalue := p_cpdf_status.ehri_employee_id;
2539 l_count := l_count+1;
2540
2541 p_tags(l_count).tagname := 'Agency_Subelement_Code';
2542 p_tags(l_count).tagvalue := p_cpdf_status.agency_code;
2543 l_count := l_count+1;
2544
2545 p_tags(l_count).tagname := 'Name_Family';
2546 p_tags(l_count).tagvalue := p_cpdf_status.employee_last_name;
2547 l_count := l_count+1;
2548
2549 p_tags(l_count).tagname := 'Name_Given';
2550 p_tags(l_count).tagvalue := p_cpdf_status.employee_first_name;
2551 l_count := l_count+1;
2552
2553 p_tags(l_count).tagname := 'Name_Middle';
2554 p_tags(l_count).tagvalue := p_cpdf_status.employee_middle_names;
2555 l_count := l_count+1;
2556
2557 p_tags(l_count).tagname := 'Name_Suffix';
2558 p_tags(l_count).tagvalue := p_cpdf_status.name_title; -- Not included since Fed doesn't allow entry.
2559 l_count := l_count+1;
2560
2561 p_tags(l_count).tagname := 'Agency_Employee_id';
2562 p_tags(l_count).tagvalue := p_cpdf_status.agency_employee_id;
2563 l_count := l_count+1;
2564
2565 p_tags(l_count).tagname := 'Veterans_Status_Code';
2566 p_tags(l_count).tagvalue := p_cpdf_status.veterans_status;
2567 l_count := l_count+1;
2568
2569 p_tags(l_count).tagname := 'Annuitant_Code';
2570 p_tags(l_count).tagvalue := p_cpdf_status.annuitant_indicator;
2571 l_count := l_count+1;
2572
2573 p_tags(l_count).tagname := 'US_Citizenship_Indicator';
2574 p_tags(l_count).tagvalue := NVL(p_cpdf_status.citizenship,'NA');
2575 l_count := l_count+1;
2576
2577 p_tags(l_count).tagname := 'Citizen_Country_Code';
2578 p_tags(l_count).tagvalue := p_cpdf_status.world_citizenship;
2579 l_count := l_count+1;
2580
2581 p_tags(l_count).tagname := 'Gender_Code';
2582 p_tags(l_count).tagvalue := p_cpdf_status.sex;
2583 l_count := l_count+1;
2584
2585 p_tags(l_count).tagname := 'Race_National_Origin_Code';
2586 p_tags(l_count).tagvalue := p_cpdf_status.race_national_origin;
2587 l_count := l_count+1;
2588
2589 p_tags(l_count).tagname := 'Disability_Code';
2590 p_tags(l_count).tagvalue := p_cpdf_status.handicap_code;
2591 l_count := l_count+1;
2592
2593 p_tags(l_count).tagname := 'Selective_Service_Registration_Indicator';
2594 p_tags(l_count).tagvalue := NVL(p_cpdf_status.slct_serv_regi_indicator,'NA');
2595 l_count := l_count+1;
2596
2597 p_tags(l_count).tagname := 'Duty_Station_Code';
2598 p_tags(l_count).tagvalue := p_cpdf_status.to_duty_station_code;
2599 l_count := l_count+1;
2600
2601 p_tags(l_count).tagname := 'Veterans_Preference_Code';
2602 p_tags(l_count).tagvalue := p_cpdf_status.veterans_preference;
2603 l_count := l_count+1;
2604
2605 p_tags(l_count).tagname := 'Service_Obligation_Type_Code_1';
2606 p_tags(l_count).tagvalue := p_cpdf_status.svc_oblig_type_code1;
2607 l_count := l_count+1;
2608
2609 p_tags(l_count).tagname := 'Service_Obligation_End_Date_1';
2610 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.svc_oblig_type_end_date1,'YYYY-MM-DD');
2611 l_count := l_count+1;
2612
2613 p_tags(l_count).tagname := 'Service_Obligation_Type_Code_2';
2614 p_tags(l_count).tagvalue := p_cpdf_status.svc_oblig_type_code2;
2615 l_count := l_count+1;
2616
2617 p_tags(l_count).tagname := 'Service_Obligation_End_Date_2';
2618 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.svc_oblig_type_end_date2,'YYYY-MM-DD');
2619 l_count := l_count+1;
2620
2621 p_tags(l_count).tagname := 'Service_Obligation_Type_Code_3';
2622 p_tags(l_count).tagvalue := p_cpdf_status.svc_oblig_type_code3;
2623 l_count := l_count+1;
2624
2625 p_tags(l_count).tagname := 'Service_Obligation_End_Date_3';
2626 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.svc_oblig_type_end_date3,'YYYY-MM-DD');
2627 l_count := l_count+1;
2628
2629 p_tags(l_count).tagname := 'Service_Obligation_Type_Code_4';
2630 p_tags(l_count).tagvalue := p_cpdf_status.svc_oblig_type_code4;
2631 l_count := l_count+1;
2632
2633 p_tags(l_count).tagname := 'Service_Obligation_End_Date_4';
2634 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.svc_oblig_type_end_date4,'YYYY-MM-DD');
2635 l_count := l_count+1;
2636
2637 p_tags(l_count).tagname := 'Instructional_Program_Code';
2638 p_tags(l_count).tagvalue := p_cpdf_status.academic_discipline;
2639 l_count := l_count+1;
2640
2641 p_tags(l_count).tagname := 'Education_Level_Code';
2642 p_tags(l_count).tagvalue := p_cpdf_status.education_level;
2643 l_count := l_count+1;
2644
2645 p_tags(l_count).tagname := 'Degree_Year';
2646 p_tags(l_count).tagvalue := p_cpdf_status.year_degree_attained;
2647 l_count := l_count+1;
2648
2649 p_tags(l_count).tagname := 'Grade_Level_Class_Rank_or_Pay_Band';
2650 p_tags(l_count).tagvalue := p_cpdf_status.to_grade_or_level;
2651 l_count := l_count+1;
2652
2653 p_tags(l_count).tagname := 'Step_or_Rate_Type_Code';
2654 p_tags(l_count).tagvalue := p_cpdf_status.to_step_or_rate;
2655 l_count := l_count+1;
2656
2657 p_tags(l_count).tagname := 'Organizational_Component_Code';
2658 p_tags(l_count).tagvalue := p_cpdf_status.organizational_component;
2659 l_count := l_count+1;
2660
2661 p_tags(l_count).tagname := 'Appointment_Type_Code';
2662 p_tags(l_count).tagvalue := p_cpdf_status.appoint_type_code;
2663 l_count := l_count+1;
2664
2665 p_tags(l_count).tagname := 'Functional_Classification_Code';
2666 p_tags(l_count).tagvalue := p_cpdf_status.functional_class;
2667 l_count := l_count+1;
2668
2669 p_tags(l_count).tagname := 'Occupational_Series_Type_Code';
2670 p_tags(l_count).tagvalue := p_cpdf_status.to_occ_code;
2671 l_count := l_count+1;
2672
2673 p_tags(l_count).tagname := 'Supervisory_Type_Code';
2674 p_tags(l_count).tagvalue := p_cpdf_status.supervisory_status;
2675 l_count := l_count+1;
2676
2677 p_tags(l_count).tagname := 'Personnel_Office_identifier_Code';
2678 p_tags(l_count).tagvalue := p_cpdf_status.personnel_office_id;
2679 l_count := l_count+1;
2680
2681 p_tags(l_count).tagname := 'Work_Schedule_Code';
2682 p_tags(l_count).tagvalue := p_cpdf_status.to_work_schedule;
2683 l_count := l_count+1;
2684
2685 p_tags(l_count).tagname := 'Part_Time_Hours';
2686 p_tags(l_count).tagvalue := p_cpdf_status.part_time_hours;
2687 l_count := l_count+1;
2688
2689 p_tags(l_count).tagname := 'Special_Rate_Supplement';
2690 p_tags(l_count).tagvalue := p_cpdf_status.to_spl_rate_supplement;
2691 l_count := l_count+1;
2692
2693 /* p_tags(l_count).tagname := 'Special_Salary_Rate';
2694 p_tags(l_count).tagvalue := p_cpdf_status.spcl_salary_rate;
2695 l_count := l_count+1; */
2696
2697 -- Begin Bug# 5011025
2698 IF p_cpdf_status.to_pay_basis <> 'PA' THEN
2699 p_tags(l_count).tagname := 'Total_Pay_Rate';
2700 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_status.to_total_salary,'99999999.99'));
2701 l_count := l_count+1;
2702
2703 p_tags(l_count).tagname := 'Basic_Pay_Amount';
2704 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_status.to_basic_pay,'99999999.99'));
2705 l_count := l_count+1;
2706
2707 p_tags(l_count).tagname := 'Adjusted_Basic_Pay_Amount';
2708 p_tags(l_count).tagvalue := ltrim(to_char(p_cpdf_status.to_adj_basic_pay,'99999999.99'));
2709 l_count := l_count+1;
2710 ELSE
2711 p_tags(l_count).tagname := 'Total_Pay_Rate';
2712 p_tags(l_count).tagvalue := p_cpdf_status.to_total_salary;
2713 l_count := l_count+1;
2714
2715 p_tags(l_count).tagname := 'Basic_Pay_Amount';
2716 p_tags(l_count).tagvalue := p_cpdf_status.to_basic_pay;
2717 l_count := l_count+1;
2718
2719 p_tags(l_count).tagname := 'Adjusted_Basic_Pay_Amount';
2720 p_tags(l_count).tagvalue := p_cpdf_status.to_adj_basic_pay;
2721 l_count := l_count+1;
2722 END IF;
2723 -- End Bug# 5011025
2724
2725 p_tags(l_count).tagname := 'Locality_Pay_Amount';
2726 p_tags(l_count).tagvalue := p_cpdf_status.to_locality_adj;
2727 l_count := l_count+1;
2728
2729 p_tags(l_count).tagname := 'Supervisor_Differential_Amount';
2730 p_tags(l_count).tagvalue := p_cpdf_status.to_supervisory_differential;
2731 l_count := l_count+1;
2732
2733 p_tags(l_count).tagname := 'Retention_Allowance_Amount';
2734 p_tags(l_count).tagvalue := p_cpdf_status.to_retention_allowance;
2735 l_count := l_count+1;
2736
2737 p_tags(l_count).tagname := 'Pay_Status_Type';
2738 p_tags(l_count).tagvalue := p_cpdf_status.pay_status;
2739 l_count := l_count+1;
2740
2741 p_tags(l_count).tagname := 'Position_Title';
2742 p_tags(l_count).tagvalue := p_cpdf_status.position_title;
2743 l_count := l_count+1;
2744
2745 p_tags(l_count).tagname := 'Position_Occupied_Code';
2746 p_tags(l_count).tagvalue := p_cpdf_status.position_occupied;
2747 l_count := l_count+1;
2748
2749 p_tags(l_count).tagname := 'Pay_Basis_Type_Code';
2750 p_tags(l_count).tagvalue := p_cpdf_status.to_pay_basis;
2751 l_count := l_count+1;
2752
2753 p_tags(l_count).tagname := 'Pay_Plan_Code';
2754 p_tags(l_count).tagvalue := p_cpdf_status.to_pay_plan;
2755 l_count := l_count+1;
2756
2757 p_tags(l_count).tagname := 'Pay_Rate_Determinant_Type_Code';
2758 p_tags(l_count).tagvalue := p_cpdf_status.to_pay_rate_determinant;
2759 l_count := l_count+1;
2760
2761 p_tags(l_count).tagname := 'Special_Pay_Table_Type_Code';
2762 p_tags(l_count).tagvalue := p_cpdf_status.spcl_pay_tbl_type;
2763 l_count := l_count+1;
2764
2765 p_tags(l_count).tagname := 'Active_Uniformed_Service_Indicator';
2766 p_tags(l_count).tagvalue := p_cpdf_status.act_svc_indicator;
2767 l_count := l_count+1;
2768
2769 p_tags(l_count).tagname := 'FLSA_Category_Code';
2770 p_tags(l_count).tagvalue := p_cpdf_status.FLSA_Category;
2771 l_count := l_count+1;
2772
2773 p_tags(l_count).tagname := 'Bargaining_Unit_Code';
2774 p_tags(l_count).tagvalue := p_cpdf_status.bargaining_unit_status;
2775 l_count := l_count+1;
2776
2777 p_tags(l_count).tagname := 'Appropriation_Code';
2778 p_tags(l_count).tagvalue := p_cpdf_status.appropriation_code;
2779 l_count := l_count+1;
2780
2781 p_tags(l_count).tagname := 'Computer_Position_Indicator';
2782 p_tags(l_count).tagvalue := NVL(p_cpdf_status.comp_pos_indicator,'NA');
2783 l_count := l_count+1;
2784
2785 p_tags(l_count).tagname := 'Military_Character_of_Service_Code';
2786 p_tags(l_count).tagvalue := p_cpdf_status.mil_char_svc_code;
2787 l_count := l_count+1;
2788
2789 p_tags(l_count).tagname := 'Military_Service_Serial_Number';
2790 p_tags(l_count).tagvalue := p_cpdf_status.mil_svc_sno;
2791 l_count := l_count+1;
2792
2793 p_tags(l_count).tagname := 'Military_Service_Start_Date';
2794 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.mil_svc_start_date,'YYYY-MM-DD');
2795 l_count := l_count+1;
2796
2797 p_tags(l_count).tagname := 'Military_Service_End_Date';
2798 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.mil_svc_end_date,'YYYY-MM-DD');
2799 l_count := l_count+1;
2800
2801 p_tags(l_count).tagname := 'Military_Branch_Type_Code';
2802 p_tags(l_count).tagvalue := p_cpdf_status.mil_branch_code;
2803 l_count := l_count+1;
2804
2805 p_tags(l_count).tagname := 'Military_Discharge_Type_Code';
2806 p_tags(l_count).tagvalue := p_cpdf_status.mil_discharge_code;
2807 l_count := l_count+1;
2808
2809 p_tags(l_count).tagname := 'Creditable_Military_Service_Years';
2810 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.creditable_military_service,1,2);
2811 l_count := l_count+1;
2812
2813 p_tags(l_count).tagname := 'Creditable_Military_Service_Months';
2814 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.creditable_military_service,3,2);
2815 l_count := l_count+1;
2816
2817 p_tags(l_count).tagname := 'Creditable_Military_Service_Days';
2818 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.creditable_military_service,5,2);
2819 l_count := l_count+1;
2820
2821 p_tags(l_count).tagname := 'Rating_of_Record_Level_Code';
2822 p_tags(l_count).tagvalue := p_cpdf_status.rating_of_record_level;
2823 l_count := l_count+1;
2824
2825 p_tags(l_count).tagname := 'Rating_of_Record_Pattern_Code';
2826 p_tags(l_count).tagvalue := p_cpdf_status.rating_of_record_pattern;
2827 l_count := l_count+1;
2828
2829 p_tags(l_count).tagname := 'Rating_Record_Period_Start_Date';
2830 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.rating_of_record_period_starts,'YYYY-MM-DD');
2831 l_count := l_count+1;
2832
2833 p_tags(l_count).tagname := 'Rating_Record_Period_End_Date';
2834 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.rating_of_record_period_ends,'YYYY-MM-DD');
2835 l_count := l_count+1;
2836
2837 p_tags(l_count).tagname := 'Tenure_Code';
2838 p_tags(l_count).tagvalue := p_cpdf_status.tenure;
2839 l_count := l_count+1;
2840
2841 p_tags(l_count).tagname := 'Career_Tenure_Authority_Code';
2842 p_tags(l_count).tagvalue := p_cpdf_status.career_tenure_code;
2843 l_count := l_count+1;
2844
2845 p_tags(l_count).tagname := 'Federal_Employees_Group_Life_Insurance_FEGLI_Code';
2846 p_tags(l_count).tagvalue := p_cpdf_status.fegli;
2847 l_count := l_count+1;
2848
2849 p_tags(l_count).tagname := 'Federal_Employees_Group_Life_Insurance_FEGLI_Life_Change_Code';
2850 p_tags(l_count).tagvalue := p_cpdf_status.fegli_life_change_code;
2851 l_count := l_count+1;
2852
2853 p_tags(l_count).tagname := 'Federal_Employees_Group_Life_Insurance_FEGLI_Life_Event_Date';
2854 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fegli_life_event_date,'YYYY-MM-DD');
2855 l_count := l_count+1;
2856
2857 p_tags(l_count).tagname := 'Federal_Employees_Group_Life_Insurance_FEGLI_Election_Effective_Date';
2858 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fegli_elect_date,'YYYY-MM-DD');
2859 l_count := l_count+1;
2860
2861 p_tags(l_count).tagname := 'Federal_Employees_Health_Benefits_FEHB_Plan_Code';
2862 p_tags(l_count).tagvalue := p_cpdf_status.health_plan;
2863 l_count := l_count+1;
2864
2865 p_tags(l_count).tagname := 'FEHB_Event_Code';
2866 p_tags(l_count).tagvalue := p_cpdf_status.fehb_event_code;
2867 l_count := l_count+1;
2868
2869 p_tags(l_count).tagname := 'Thrift_Savings_Plan_TSP_Eligibility_Date';
2870 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.tsp_eligibility_date,'YYYY-MM-DD');
2871 l_count := l_count+1;
2872
2873 p_tags(l_count).tagname := 'Thrift_Savings_Plan_TSP_Effective_Date';
2874 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.tsp_effective_date,'YYYY-MM-DD');
2875 l_count := l_count+1;
2876
2877 p_tags(l_count).tagname := 'Thrift_Savings_Plan_TSP_Election_Contribution_Percent';
2878 p_tags(l_count).tagvalue := p_cpdf_status.tsp_elect_contrib_pct;
2879 l_count := l_count+1;
2880
2881 p_tags(l_count).tagname := 'Thrift_Savings_Plan_TSP_Election_Contribution_Amount';
2882 p_tags(l_count).tagvalue := p_cpdf_status.tsp_emp_amount;
2883 l_count := l_count+1;
2884
2885 p_tags(l_count).tagname := 'Federal_Employees_Retirement_System_FERS_Coverage_Code';
2886 p_tags(l_count).tagvalue := p_cpdf_status.fers_coverage;
2887 l_count := l_count+1;
2888
2889 p_tags(l_count).tagname := 'Federal_Employees_Retirement_System_FERS_Elect_Date';
2890 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fers_elect_date,'YYYY-MM-DD');
2891 l_count := l_count+1;
2892
2893 p_tags(l_count).tagname := 'Federal_Employees_Retirement_System_FERS_Election_Indicator';
2894 p_tags(l_count).tagvalue := p_cpdf_status.fers_elect_indicator;
2895 l_count := l_count+1;
2896
2897 p_tags(l_count).tagname := 'Retained_Grade_Level_Class_Rank_or_Pay_Brand_Code';
2898 p_tags(l_count).tagvalue := p_cpdf_status.retained_grade_or_level;
2899 l_count := l_count+1;
2900
2901 p_tags(l_count).tagname := 'Retained_Pay_Plan_Code';
2902 p_tags(l_count).tagvalue := p_cpdf_status.retained_pay_plan;
2903 l_count := l_count+1;
2904
2905 p_tags(l_count).tagname := 'Retained_Step_or_Rate_Type_Code';
2906 p_tags(l_count).tagvalue := p_cpdf_status.retained_step_or_rate;
2907 l_count := l_count+1;
2908
2909 /*
2910 -- Bug 4469808
2911 p_tags(l_count).tagname := 'Benefits_Continuation_Annual_Leave_Balance_Indicator';
2912 p_tags(l_count).tagvalue := p_cpdf_status.alb_indicator;
2913 l_count := l_count+1;
2914
2915 p_tags(l_count).tagname := 'Benefits_Continuation_Annual_Leave_Balance_Election_Date';
2916 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.alb_elect_date,'YYYY-MM-DD');
2917 l_count := l_count+1;
2918
2919 p_tags(l_count).tagname := 'Benefits_Continuation_Annual_Leave_Balance_Election_Notification_Date';
2920 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.alb_notify_date,'YYYY-MM-DD');
2921 l_count := l_count+1;
2922 */
2923
2924 p_tags(l_count).tagname := 'Benefits_Continuation_Federal_Employees_Group_Life_Insurance_FEGLI_Indicator';
2925 p_tags(l_count).tagvalue := NVL(p_cpdf_status.fegli_indicator,'NA');
2926 l_count := l_count+1;
2927
2928 p_tags(l_count).tagname := 'Benefits_Continuation_Employees_Group_Life_Insurance_FEGLI_Election_Date';
2929 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fegli_elect_date,'YYYY-MM-DD');
2930 l_count := l_count+1;
2931
2932 p_tags(l_count).tagname := 'Benefits_Continuation_Employees_Group_Life_Insurance_FEGLI_Election_Notification_Date';
2933 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fegli_notify_date,'YYYY-MM-DD');
2934 l_count := l_count+1;
2935
2936 p_tags(l_count).tagname := 'Benefits_Continuation_Federal_Employee_Health_Benefits_FEHB_Indicator';
2937 p_tags(l_count).tagvalue := NVL(p_cpdf_status.fehb_indicator,'NA');
2938 l_count := l_count+1;
2939
2940 p_tags(l_count).tagname := 'Benefits_Continuation_Federal_Employee_Health_Benefits_FEHB_Election_Date';
2941 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fehb_elect_date,'YYYY-MM-DD');
2942 l_count := l_count+1;
2943
2944
2945 p_tags(l_count).tagname := 'Benefits_Continuation_Federal_Employee_Health_Benefits_FEHB_Election_Notification_Date';
2946 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fehb_notify_date,'YYYY-MM-DD');
2947 l_count := l_count+1;
2948
2949 p_tags(l_count).tagname := 'Benefits_Continuation_Retirement_Indicator';
2950 p_tags(l_count).tagvalue := NVL(p_cpdf_status.retire_indicator,'NA');
2951 l_count := l_count+1;
2952
2953 p_tags(l_count).tagname := 'Benefits_Continuation_Retirement_Election_Date';
2954 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.retire_elect_date,'YYYY-MM-DD');
2955 l_count := l_count+1;
2956
2957 p_tags(l_count).tagname := 'Benefits_Continuation_Retirement_Election_Notification_Date';
2958 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.retire_notify_date,'YYYY-MM-DD');
2959 l_count := l_count+1;
2960
2961 /*
2962 -- Bug 4469808
2963 p_tags(l_count).tagname := 'Benefits_Continuation_Election_Date';
2964 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.cont_elect_date,'YYYY-MM-DD');
2965 l_count := l_count+1;
2966
2967 p_tags(l_count).tagname := 'Benefits_Continuation_Election_Notification_Date';
2968 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.cont_notify_date,'YYYY-MM-DD');
2969 l_count := l_count+1;
2970 */
2971
2972 p_tags(l_count).tagname := 'Benefits_Continuation_Termination_Insufficient_Pay_Election_Date';
2973 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.cont_term_elect_date,'YYYY-MM-DD');
2974 l_count := l_count+1;
2975
2976 p_tags(l_count).tagname := 'Benefits_Continuation_Termination_Insufficient_Pay_Notification_Date';
2977 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.cont_ins_pay_notify_date,'YYYY-MM-DD');
2978 l_count := l_count+1;
2979
2980 p_tags(l_count).tagname := 'Benefits_Continuation_Termination_Insufficient_Pay_Payment_Type_Code';
2981 p_tags(l_count).tagvalue := p_cpdf_status.cont_pay_type_code;
2982 l_count := l_count+1;
2983
2984 p_tags(l_count).tagname := 'Retirement_Service_Computation_Date';
2985 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.scd_retirement,'YYYY-MM-DD');
2986 l_count := l_count+1;
2987
2988 p_tags(l_count).tagname := 'RIF_Service_Computation_Date';
2989 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.SCD_RIF,'YYYY-MM-DD');
2990 l_count := l_count+1;
2991
2992 p_tags(l_count).tagname := 'SES_Service_Computation_Date';
2993 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.scd_ses,'YYYY-MM-DD');
2994 l_count := l_count+1;
2995
2996 p_tags(l_count).tagname := 'Special_Retirement_Service_Computation_Date';
2997 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.scd_spcl_retire,'YYYY-MM-DD');
2998 l_count := l_count+1;
2999
3000 p_tags(l_count).tagname := 'Leave_Service_Computation_Date';
3001 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.leave_scd,'YYYY-MM-DD');
3002 l_count := l_count+1;
3003
3004 p_tags(l_count).tagname := 'Thrift_Savings_Plan_Service_Computation_Date';
3005 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.tsp_scd,'YYYY-MM-DD');
3006 l_count := l_count+1;
3007
3008 p_tags(l_count).tagname := 'Intergovernmental_Personnel_Act_IPA_Entitlements_Benefits_Notification_Text';
3009 p_tags(l_count).tagvalue := null; -- Add to Temp too!
3010 l_count := l_count+1;
3011
3012 p_tags(l_count).tagname := 'Retirement_System_Type_Code';
3013 p_tags(l_count).tagvalue := p_cpdf_status.retirement_plan;
3014 l_count := l_count+1;
3015
3016 p_tags(l_count).tagname := 'Disability_Retirement_Notification_Date';
3017 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.disability_retire_notify,'YYYY-MM-DD');
3018 l_count := l_count+1;
3019
3020 p_tags(l_count).tagname := 'Frozen_Service_Years';
3021 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.frozen_service,1,2);
3022 l_count := l_count+1;
3023
3024 p_tags(l_count).tagname := 'Frozen_Service_Months';
3025 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.frozen_service,3,2);
3026 l_count := l_count+1;
3027
3028 p_tags(l_count).tagname := 'Frozen_Service_Days';
3029 p_tags(l_count).tagvalue := SUBSTR(p_cpdf_status.frozen_service,5,2);
3030 l_count := l_count+1;
3031
3032 p_tags(l_count).tagname := 'Current_Appointment_Authority_Code_1';
3033 p_tags(l_count).tagvalue := p_cpdf_status.current_appointment_auth1;
3034 l_count := l_count+1;
3035
3036 p_tags(l_count).tagname := 'Current_Appointment_Authority_Code_2';
3037 p_tags(l_count).tagvalue := p_cpdf_status.current_appointment_auth2;
3038 l_count := l_count+1;
3039
3040 p_tags(l_count).tagname := 'Work_Address_Line_1';
3041 p_tags(l_count).tagvalue := p_cpdf_status.work_address_line1;
3042 l_count := l_count+1;
3043
3044 p_tags(l_count).tagname := 'Work_Address_Line_2';
3045 p_tags(l_count).tagvalue := p_cpdf_status.work_address_line2;
3046 l_count := l_count+1;
3047
3048 p_tags(l_count).tagname := 'Work_Address_Line_3';
3049 p_tags(l_count).tagvalue := p_cpdf_status.work_address_line3;
3050 l_count := l_count+1;
3051
3052 p_tags(l_count).tagname := 'Work_Address_Line_4';
3053 p_tags(l_count).tagvalue := p_cpdf_status.work_address_line4;
3054 l_count := l_count+1;
3055
3056 p_tags(l_count).tagname := 'Work_City';
3057 p_tags(l_count).tagvalue := p_cpdf_status.work_city;
3058 l_count := l_count+1;
3059
3060 p_tags(l_count).tagname := 'Work_Geographic_Locator_Code';
3061 IF p_cpdf_status.work_address_line1 IS NOT NULL THEN --Bug# 6973541
3062 p_tags(l_count).tagvalue := p_cpdf_status.to_duty_station_code;
3063 END IF; --Bug# 6973541
3064 l_count := l_count+1;
3065
3066 p_tags(l_count).tagname := 'Work_State_Code';
3067 p_tags(l_count).tagvalue := p_cpdf_status.work_state_code;
3068 l_count := l_count+1;
3069
3070 p_tags(l_count).tagname := 'Work_Postal_Code';
3071 p_tags(l_count).tagvalue := p_cpdf_status.work_postal_code;
3072 l_count := l_count+1;
3073
3074 p_tags(l_count).tagname := 'Work_Region';
3075 p_tags(l_count).tagvalue := p_cpdf_status.work_region; --Bug# 4725292
3076 l_count := l_count+1;
3077
3078 p_tags(l_count).tagname := 'Work_Country_Code';
3079 p_tags(l_count).tagvalue := p_cpdf_status.work_country_code;
3080 l_count := l_count+1;
3081
3082 p_tags(l_count).tagname := 'Employee_Work_Email';
3083 p_tags(l_count).tagvalue := p_cpdf_status.work_employee_email;
3084 l_count := l_count+1;
3085
3086 p_tags(l_count).tagname := 'Work_Phone_Number';
3087 p_tags(l_count).tagvalue := p_cpdf_status.work_phone_number;
3088 l_count := l_count+1;
3089
3090 p_tags(l_count).tagname := 'Home_Phone_Number';
3091 p_tags(l_count).tagvalue := p_cpdf_status.home_phone_number;
3092 l_count := l_count+1;
3093
3094 p_tags(l_count).tagname := 'Cell_Phone_Number';
3095 p_tags(l_count).tagvalue := p_cpdf_status.cell_phone_number;
3096 l_count := l_count+1;
3097
3098 p_tags(l_count).tagname := 'Emergency_Contact_Family_Name_1';
3099 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_family_name1;
3100 l_count := l_count+1;
3101
3102 p_tags(l_count).tagname := 'Emergency_Contact_Given_Name_1';
3103 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_given_name1;
3104 l_count := l_count+1;
3105
3106 p_tags(l_count).tagname := 'Emergency_Contact_Middle_Name_1';
3107 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_middle_name1;
3108 l_count := l_count+1;
3109
3110 p_tags(l_count).tagname := 'Emergency_Contact_Name_Suffix_1';
3111 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_suffix1;
3112 l_count := l_count+1;
3113
3114 p_tags(l_count).tagname := 'Emergency_Contact_Information_Update_Date_1';
3115 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.emrgncy_cntct_infrm_upd_dt1,'YYYY-MM-DD');
3116 l_count := l_count+1;
3117
3118 p_tags(l_count).tagname := 'Emergency_Contact_Phone_Number_1';
3119 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_phone1;
3120 l_count := l_count+1;
3121
3122 p_tags(l_count).tagname := 'Emergency_Contact_Family_Name_2';
3123 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_family_name2;
3124 l_count := l_count+1;
3125
3126 p_tags(l_count).tagname := 'Emergency_Contact_Given_Name_2';
3127 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_given_name2;
3128 l_count := l_count+1;
3129
3130 p_tags(l_count).tagname := 'Emergency_Contact_Middle_Name_2';
3131 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_middle_name2;
3132 l_count := l_count+1;
3133
3134 p_tags(l_count).tagname := 'Emergency_Contact_Name_Suffix_2';
3135 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_suffix2;
3136 l_count := l_count+1;
3137
3138 p_tags(l_count).tagname := 'Emergency_Contact_Information_Update_Date_2';
3139 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.emrgncy_cntct_infrm_upd_dt2,'YYYY-MM-DD');
3140 l_count := l_count+1;
3141
3142 p_tags(l_count).tagname := 'Emergency_Contact_Phone_Number_2';
3143 p_tags(l_count).tagvalue := p_cpdf_status.emrgncy_cntct_phone2;
3144 l_count := l_count+1;
3145
3146 p_tags(l_count).tagname := 'Language_Code_1';
3147 p_tags(l_count).tagvalue := p_cpdf_status.language_code1;
3148 l_count := l_count+1;
3149
3150 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_1';
3151 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type1;
3152 l_count := l_count+1;
3153
3154 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_1';
3155 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level1;
3156 l_count := l_count+1;
3157
3158 p_tags(l_count).tagname := 'Language_Code_2';
3159 p_tags(l_count).tagvalue := p_cpdf_status.language_code2;
3160 l_count := l_count+1;
3161
3162 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_2';
3163 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type2;
3164 l_count := l_count+1;
3165
3166 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_2';
3167 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level2;
3168 l_count := l_count+1;
3169
3170 p_tags(l_count).tagname := 'Language_Code_3';
3171 p_tags(l_count).tagvalue := p_cpdf_status.language_code3;
3172 l_count := l_count+1;
3173
3174 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_3';
3175 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type3;
3176 l_count := l_count+1;
3177
3178 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_3';
3179 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level3;
3180 l_count := l_count+1;
3181
3182 p_tags(l_count).tagname := 'Language_Code_4';
3183 p_tags(l_count).tagvalue := p_cpdf_status.language_code4;
3184 l_count := l_count+1;
3185
3186 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_4';
3187 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type4;
3188 l_count := l_count+1;
3189
3190 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_4';
3191 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level4;
3192 l_count := l_count+1;
3193
3194 p_tags(l_count).tagname := 'Language_Code_5';
3195 p_tags(l_count).tagvalue := p_cpdf_status.language_code5;
3196 l_count := l_count+1;
3197
3198 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_5';
3199 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type5;
3200 l_count := l_count+1;
3201
3202 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_5';
3203 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level5;
3204 l_count := l_count+1;
3205
3206 p_tags(l_count).tagname := 'Language_Code_6';
3207 p_tags(l_count).tagvalue := p_cpdf_status.language_code6;
3208 l_count := l_count+1;
3209
3210 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_6';
3211 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type6;
3212 l_count := l_count+1;
3213
3214 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_6';
3215 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level6;
3216 l_count := l_count+1;
3217
3218 p_tags(l_count).tagname := 'Language_Code_7';
3219 p_tags(l_count).tagvalue := p_cpdf_status.language_code7;
3220 l_count := l_count+1;
3221
3222 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_7';
3223 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type7;
3224 l_count := l_count+1;
3225
3226 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_7';
3227 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level7;
3228 l_count := l_count+1;
3229
3230 p_tags(l_count).tagname := 'Language_Code_8';
3231 p_tags(l_count).tagvalue := p_cpdf_status.language_code8;
3232 l_count := l_count+1;
3233
3234 p_tags(l_count).tagname := 'Language_Proficiency_Type_Code_8';
3235 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_type8;
3236 l_count := l_count+1;
3237
3238 p_tags(l_count).tagname := 'Language_Proficiency_Level_Code_8';
3239 p_tags(l_count).tagvalue := p_cpdf_status.lang_prof_level8;
3240 l_count := l_count+1;
3241
3242 -- Bug 4714292 EHRI Reports Changes for EOY 05
3243 p_tags(l_count).tagname := 'Ethnicity_Code';
3244 p_tags(l_count).tagvalue := p_cpdf_status.race_ethnic_info;
3245 l_count := l_count+1;
3246 -- End Bug 4714292 EHRI Reports Changes for EOY 05
3247 --Bug 6158983 EHRI Report Changes
3248 p_tags(l_count).tagname := 'Benefits_Continuation_Federal_Employees_Health_Benefits_FEHB_Election_Effective_Date';
3249 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.fehb_elect_eff_date,'YYYY-MM-DD');
3250 l_count := l_count+1;
3251
3252 p_tags(l_count).tagname := 'Appointment_Not_to_Exceed_NTE_Date';
3253 p_tags(l_count).tagvalue := TO_CHAR(p_cpdf_status.appointment_nte_date,'YYYY-MM-DD');
3254 l_count := l_count+1;
3255
3256 --End Bug 6158983 EHRI Report Changes
3257 --Begin bug# 14245991
3258 p_tags(l_count).tagname := 'Telework_Indicator';
3259 p_tags(l_count).tagvalue := p_cpdf_status.Telework_Indicator;
3260 l_count := l_count+1;
3261 --End bug# 14245991
3262
3263 END WriteTagValues;
3264
3265 -----------------------------------------------------------------------------
3266 -- Writing the records from PL/SQL table p_tags into XML File
3267 -----------------------------------------------------------------------------
3268 PROCEDURE WriteXMLvalues(p_l_fp utl_file.file_type, p_tags t_tags )
3269 IS
3270 BEGIN
3271 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
3272 utl_file.put_line(p_l_fp,'<' || p_tags(l_tags).tagname || '>' || p_tags(l_tags).tagvalue || '</' || p_tags(l_tags).tagname || '>');
3273 END LOOP;
3274 END;
3275
3276 -----------------------------------------------------------------------------
3277 -- Writing the records from PL/SQL table p_tags into Text and FND Output File
3278 -----------------------------------------------------------------------------
3279 PROCEDURE WriteAsciivalues(p_l_fp utl_file.file_type, p_tags t_tags,p_gen_txt_file IN VARCHAR2 )
3280 IS
3281 l_temp VARCHAR2(4000);
3282 l_tot NUMBER;
3283 BEGIN
3284 l_tot := p_tags.COUNT;
3285 IF l_tot > 0 THEN
3286 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
3287 IF l_tags = l_tot THEN
3288 l_temp := p_tags(l_tags).tagvalue;
3289 IF p_gen_txt_file = 'Y' THEN
3290 utl_file.put_line(p_l_fp,l_temp);
3291 END IF;
3292 fnd_file.put_line(fnd_file.output,l_temp);
3293 ELSE
3294 l_temp := p_tags(l_tags).tagvalue || '|';
3295 IF p_gen_txt_file = 'Y' THEN
3296 utl_file.put(p_l_fp,l_temp);
3297 END IF;
3298 fnd_file.put(fnd_file.output,l_temp);
3299 END IF;
3300 END LOOP;
3301 END IF;
3302
3303 END WriteAsciivalues;
3304
3305 --Bug# 8486208 added the following procedure
3306 PROCEDURE get_agencies_from_group(p_agency_group IN VARCHAR2,
3307 p_agencies_with_se OUT NOCOPY VARCHAR2,
3308 p_agencies_without_se OUT NOCOPY VARCHAR2)
3309 IS
3310 l_agencies_with_se varchar2(240);
3311 l_agencies_without_se varchar2(240);
3312 l_prev NUMBER;
3313 l_next NUMBER;
3314 l_no_of_char NUMBER;
3315
3316 BEGIN
3317 l_agencies_with_se := NULL;
3318 l_agencies_without_se := NULL;
3319 l_prev :=1;
3320
3321 loop
3322 l_next := instr(p_agency_group,',',l_prev);
3323 if l_next = 0 then
3324 l_next := length(p_agency_group)+1;
3325 end if;
3326 l_no_of_char := l_next -l_prev;
3327
3328 if l_no_of_char > 2 then
3329 if l_agencies_with_se is NULL then
3330 l_agencies_with_se := substr(p_agency_group,l_prev,l_no_of_char);
3331 else
3332 l_agencies_with_se := l_agencies_with_se||','||substr(p_agency_group,l_prev,l_no_of_char);
3333 end if;
3334 else
3335 if l_agencies_without_se is NULL then
3336 l_agencies_without_se := substr(p_agency_group,l_prev,l_no_of_char);
3337 else
3338 l_agencies_without_se := l_agencies_without_se||','||substr(p_agency_group,l_prev,l_no_of_char);
3339 end if;
3340 end if;
3341 if l_next > length(p_agency_group) then
3342 exit;
3343 end if;
3344 l_prev := l_next+1;
3345 end loop;
3346
3347 p_agencies_with_se := l_agencies_with_se;
3348 p_agencies_without_se := l_agencies_without_se;
3349
3350 END;
3351
3352 ---------------------------------------------------------------------------------------------
3353 -- This is the procedure to populate values into the temporary table GHR_CPDF_TEMP
3354 ---------------------------------------------------------------------------------------------
3355 -- Bug 8486208 modified for new dynamic parameter
3356 PROCEDURE populate_ghr_cpdf_temp (p_agency IN VARCHAR2,
3357 p_agency_group IN VARCHAR2,
3358 p_report_date IN DATE,
3359 p_count_only IN BOOLEAN)
3360
3361 IS
3362 l_proc varchar2(30) := 'populate_ghr_cpdf_temp';
3363
3364 CURSOR all_assignments_cur(p_agencies_with_se in varchar2,
3365 p_agencies_without_se in varchar2)
3366 is
3367 SELECT asg.assignment_id,
3368 asg.person_id,
3369 asg.position_id,
3370 asg.grade_id,
3371 asg.job_id,
3372 asg.location_id,
3373 asg.effective_start_date,
3374 asg.business_group_id,
3375 ast.per_system_status assignment_status_type,
3376 ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) agency_code
3377 FROM PER_ALL_ASSIGNMENTS asg, per_assignment_status_types ast -- Changing from per_assignments_f
3378 WHERE ast.assignment_status_type_id = asg.assignment_status_type_id
3379 -- only consider "Active" assignments as defined by below, also only look at
3380 -- assignments that are assigned to a valid person as of the report date.
3381 AND g_report_date between asg.effective_start_date and asg.effective_end_date
3382 AND ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
3383 AND asg.assignment_type <> 'B'
3384 AND asg.position_id IS NOT NULL
3385 --Bug 8486208 modified for new dynamic parameter
3386 AND ((p_agency is not null and
3387 ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) like p_agency)
3388 OR
3389 (p_agencies_with_se is not null and INSTR(p_agencies_with_se,ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id),1) > 0)
3390 OR
3391 (p_agencies_without_se is not null and INSTR(p_agencies_without_se,substr(ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id),1,2),1) > 0)
3392 )
3393 AND decode(hr_general.get_xbg_profile,'Y',asg.business_group_id , hr_general.get_business_group_id) = asg.business_group_id
3394 ORDER BY assignment_id;
3395 -- Bug 3704123 - Adding order by clause for the above statement so that results will be in temp segment
3396
3397 l_all_assignments_rec all_assignments_cur%ROWTYPE;
3398 --- 3671043 Bug fix
3399 l_log_text ghr_process_log.log_text%type;
3400 l_message_name ghr_process_log.message_name%type;
3401 l_log_date ghr_process_log.log_date%type;
3402
3403 CURSOR cur_per_details(p_person_id per_all_people.person_id%type)
3404 IS
3405 --bug # 9329643 added employee number
3406 SELECT pap.full_name name ,pap.national_identifier ssn,pap.last_name,pap.first_name
3407 ,pap.middle_names, pap.title, pap.employee_number
3408 FROM per_all_people pap
3409 WHERE p_person_id = pap.person_id
3410 AND p_report_date between pap.effective_start_date AND pap.effective_end_date
3411 AND g_business_group_id = pap.business_group_id;
3412
3413 --Bug# 6158983
3414 --Bug# 6477035
3415 /*Cursor nte_date
3416 is
3417 SELECT first_noa_information1
3418 FROM ghr_pa_requests
3419 WHERE employee_assignment_id = g_assignment_id
3420 AND pa_notification_id is not null
3421 AND person_id = g_person_id
3422 AND to_position_id = g_position_id
3423 AND noa_family_code = 'APP'
3424 AND first_noa_information1 like '____%__%__ __:__:__'
3425 AND fnd_date.canonical_to_date(first_noa_information1) >= g_report_date;*/
3426
3427 Cursor nte_date
3428 is
3429 SELECT aei_information4
3430 FROM ghr_assignment_extra_info_h_v
3431 WHERE pa_history_id = (SELECT max(pa_history_id)
3432 FROM ghr_assignment_extra_info_h_v ASG,
3433 ghr_nature_of_actions NAT
3434 WHERE information_type = 'GHR_US_ASG_NTE_DATES'
3435 AND asg.nature_of_action_id = nat.nature_of_action_id
3436 AND (code LIKE '1%' OR code LIKE '5%' OR code IN ('750','760','761','762','765'))
3437 AND aei_information4 IS NOT NULL
3438 AND assignment_id = g_assignment_id
3439 AND person_id = g_person_id)
3440 AND fnd_date.canonical_to_date(aei_information4) >= g_report_date;
3441 --Bug# 6477035
3442
3443 --Bug# 6158983
3444
3445
3446
3447 l_full_name per_all_people.full_name%type;
3448 l_ssn per_all_people.national_identifier%type;
3449 l_records_found BOOLEAN;
3450 l_mesgbuff1 VARCHAR2(4000);
3451 l_assignment_status_type VARCHAR2(200);
3452 l_suffix VARCHAR2(30) := NULL;
3453 l_last_name VARCHAR2(150) := NULL;
3454 --Bug # 9329643
3455 l_employee_number per_people_f.employee_number%TYPE;
3456
3457 -- FWFA Changes Declare variable l_calc_pay_table_id
3458
3459 Cursor c_pay_table_name (p_user_table_id number) is
3460 SELECT SUBSTR(user_table_name,1,4) user_table_name
3461 FROM pay_user_tables
3462 WHERE user_table_id = p_user_table_id;
3463
3464 l_calc_pay_table_id VARCHAR2(4);
3465
3466 --8486208
3467 l_agencies_with_se VARCHAR2(240);
3468 l_agencies_without_se VARCHAR2(240);
3469
3470
3471 -- This function returns true if the Pay Plan passed in is an 'GS' equivalent
3472 FUNCTION pp_gs_equivalent (p_pay_plan IN VARCHAR2)
3473 RETURN BOOLEAN IS
3474 CURSOR cur_ppl IS
3475 SELECT 1
3476 FROM ghr_pay_plans ppl
3477 WHERE ppl.pay_plan = p_pay_plan
3478 AND ppl.equivalent_pay_plan = 'GS';
3479 --
3480 BEGIN
3481 FOR cur_ppl_rec IN cur_ppl LOOP
3482 RETURN(TRUE);
3483 END LOOP;
3484 --
3485 RETURN(FALSE);
3486 END pp_gs_equivalent;
3487
3488
3489 BEGIN
3490 hr_utility.set_location('Entering:'||l_proc,5);
3491 ghr_mto_int.set_log_program_name('GHR_CPDF_EHRI_STATRPT');
3492 g_report_date := p_report_date;
3493 g_agency := p_agency;
3494 l_records_found:=FALSE;
3495 cleanup_table;
3496 initialize_record;
3497
3498 --8486208 added the following to get agencies with sub elements and with out sub elements
3499 if p_agency_group is not null then
3500 get_agencies_from_group(UPPER(p_agency_group),l_agencies_with_se, l_agencies_without_se);
3501 end if;
3502 FOR l_all_assignments_rec IN all_assignments_cur(l_agencies_with_se,l_agencies_without_se)
3503 LOOP
3504
3505 BEGIN
3506 -- initialize every iteration
3507 initialize_record;
3508 -- assign globals
3509 g_assignment_id := l_all_assignments_rec.assignment_id;
3510
3511 --dbms_output.put_line('Assignment_id = '||g_assignment_id);
3512 g_person_id := l_all_assignments_rec.person_id;
3513 g_position_id := l_all_assignments_rec.position_id;
3514 g_grade_id := l_all_assignments_rec.grade_id;
3515 g_job_id := l_all_assignments_rec.job_id;
3516 g_location_id := l_all_assignments_rec.location_id;
3517 g_ghr_cpdf_temp.agency_code := l_all_assignments_rec.agency_code;
3518 g_business_group_id := l_all_assignments_rec.business_group_id;
3519 l_assignment_status_type := l_all_assignments_rec.assignment_status_type;
3520 -- Bug 714944 -- No not report on NAF positions:
3521 IF ghr_cpdf_dynrpt.exclude_position (p_position_id => g_position_id
3522 ,p_effective_date => g_report_date) THEN
3523 GOTO end_asg_loop; -- loop for the next one!
3524
3525 END IF;
3526
3527 l_message_name := NULL;
3528 g_message_name := NULL; --Bug# 4753092
3529 l_records_found:=TRUE;
3530 --
3531 --
3532 BEGIN
3533
3534 FOR per_det in cur_per_details(g_person_id)
3535 LOOP
3536 -- Bug# 4648811 extracting suffix from the lastname and also removing suffix from lastname
3537 get_suffix_lname(per_det.last_name,
3538 p_report_date,
3539 l_suffix,
3540 l_last_name);
3541 --End Bug# 4648811
3542
3543 g_ghr_cpdf_temp.employee_last_name := l_last_name;
3544 g_ghr_cpdf_temp.employee_first_name := per_det.first_name;
3545 g_ghr_cpdf_temp.employee_middle_names := per_det.middle_names;
3546 g_ghr_cpdf_temp.name_title := l_suffix;
3547 END LOOP;
3548
3549 -- BUG # 13532887 Commented as it retreived from Pay user ra status
3550 /* IF l_assignment_status_type = 'ACTIVE_ASSIGN' THEN
3551 g_ghr_cpdf_temp.pay_status := 'P';
3552 ELSIF l_assignment_status_type = 'SUSP_ASSIGN' THEN
3553 g_ghr_cpdf_temp.pay_status := 'N';
3554 END IF;*/
3555
3556 -- Begin Bug# 4753092
3557 g_message_name := 'Fetch Position title';
3558 -- End Bug# 4753092
3559 g_ghr_cpdf_temp.position_title := ghr_api.get_position_title_pos(
3560 p_position_id => g_position_id,
3561 p_business_group_id => g_business_group_id,
3562 p_effective_date => g_report_date);
3563
3564 --
3565 -- Bug 3671043 Handling Exceptions (madhuri)
3566 -- Bug# 4753092
3567 g_message_name := 'Fetch Appointment date';
3568 --dbms_output.put_line(l_message_name);
3569 get_appointment_date(p_person_id => g_person_id
3570 ,p_report_date => g_report_date
3571 ,p_appointment_date => g_appointment_date);
3572 --
3573 -- call fetch routines to populate record
3574 -- Bug# 4753092 commented below statment
3575 --l_message_name := 'get_from_history_asgnei';
3576 --dbms_output.put_line(l_message_name);
3577 get_from_history_asgnei
3578 (p_sr_assignment_id => g_assignment_id
3579 ,p_sr_report_date => g_report_date
3580 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3581 );
3582 -- FWFA Change Get pay table id
3583 -- Bug#5063289 Fetch the First 4 characters of Pay table name.
3584 FOR pay_table_rec IN c_pay_table_name(g_ghr_cpdf_temp.to_pay_table_id)
3585 LOOP
3586 l_calc_pay_table_id := pay_table_rec.user_table_name;
3587 END LOOP;
3588 --Begin Bug# 4753092
3589 --l_message_name := 'get_from_history_payele';
3590 --End Bug# 4753092
3591 --dbms_output.put_line(l_message_name);
3592 get_from_history_payele
3593 (p_sr_assignment_id => g_assignment_id
3594 ,p_sr_report_date => g_report_date
3595 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp);
3596
3597 --Begin Bug# 4753092
3598 g_message_name := 'Fetch Person Details';
3599 --l_message_name := 'get_from_history_people';
3600 --End Bug# 4753092
3601 --dbms_output.put_line(l_message_name);
3602 get_from_history_people
3603 (p_sr_person_id => g_person_id
3604 ,p_sr_report_date => g_report_date
3605 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3606 ); -- g_ghr_cpdf_temp.to_national_identifier
3607
3608 --Begin Bug# 4753092
3609 --l_message_name := 'get_from_history_ancrit';
3610 --End Bug# 4753092
3611 --dbms_output.put_line(l_message_name);
3612 get_from_history_ancrit
3613 (p_sr_person_id => g_person_id
3614 ,p_sr_report_date => g_report_date
3615 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3616 );
3617 --Begin Bug# 4753092
3618 --l_message_name := 'get_from_history_peopei';
3619 --End Bug# 4753092
3620 --dbms_output.put_line(l_message_name);
3621 get_from_history_peopei
3622 (p_sr_person_id => g_person_id
3623 ,p_sr_report_date => g_report_date
3624 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3625 );
3626 --Begin Bug# 4725292
3627 get_from_per_wrkadd
3628 (p_sr_person_id => g_person_id
3629 ,p_sr_report_date => g_report_date
3630 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3631 );
3632 --End Bug# 4725292
3633 IF g_position_id IS NOT NULL
3634 THEN
3635 -- Begin Bug# 4753092
3636 -- l_message_name := 'get_from_history_posiei';
3637 -- End Bug# 4753092
3638 --dbms_output.put_line(l_message_name);
3639 get_from_history_posiei
3640 (p_sr_position_id => g_position_id
3641 ,p_sr_report_date => g_report_date
3642 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3643 );
3644 END IF;
3645
3646 IF g_grade_id IS NOT NULL
3647 THEN
3648 -- Begin Bug# 4753092
3649 g_message_name := 'Fetch Grade Details';
3650 --l_message_name := 'get_from_history_gradef';
3651 -- End Bug# 4753092
3652 --dbms_output.put_line(l_message_name);
3653 get_from_history_gradef
3654 (p_sr_grade_id => g_grade_id
3655 ,p_sr_report_date => g_report_date
3656 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3657 );
3658 END IF;
3659
3660 IF g_job_id IS NOT NULL
3661 THEN
3662 -- Begin Bug# 4753092
3663 g_message_name := 'Fetch Job Details';
3664 --l_message_name := 'get_from_history_jobdef';
3665 -- End Bug# 4753092
3666 --dbms_output.put_line(l_message_name);
3667 get_from_history_jobdef
3668 (p_sr_job_id => g_job_id
3669 ,p_sr_report_date => g_report_date
3670 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3671 );
3672 END IF;
3673
3674 IF g_location_id IS NOT NULL
3675 THEN
3676 -- Begin Bug# 4753092
3677 --l_message_name := 'get_from_history_dutsta';
3678 -- End Bug# 4753092
3679 --dbms_output.put_line(l_message_name);
3680 get_from_history_dutsta
3681 (p_sr_location_id => g_location_id
3682 ,p_sr_report_date => g_report_date
3683 ,p_sr_ghr_cpdf_temp => g_ghr_cpdf_temp
3684 );
3685 END IF;
3686
3687
3688 -- Bug 4714292 EHRI Reports Changes for EOY 05
3689 IF g_ghr_cpdf_temp.to_pay_rate_determinant IN ('5','6','E','F') THEN
3690 hr_utility.set_location('Inside PRD ' || g_ghr_cpdf_temp.to_locality_adj,111 );
3691 g_ghr_cpdf_temp.to_spl_rate_supplement := g_ghr_cpdf_temp.to_locality_adj;
3692 g_ghr_cpdf_temp.to_locality_adj := NULL;
3693 ELSE
3694 g_ghr_cpdf_temp.to_spl_rate_supplement := NULL;
3695 END IF;
3696 -- End Bug 4714292 EHRI Reports Changes for EOY 05
3697
3698 -- If Ethnicity is reported, RNO should be null
3699 IF g_ghr_cpdf_temp.race_ethnic_info IS NOT NULL THEN
3700 g_ghr_cpdf_temp.race_national_origin := NULL;
3701 END IF;
3702
3703 -- FWFA Change Override pay table id with that retrieved from Assignment
3704 -- Bug#5036001
3705 IF l_calc_pay_table_id IS NOT NULL THEN
3706 IF pp_gs_equivalent(g_to_pay_plan) AND
3707 g_ghr_cpdf_temp.to_pay_rate_determinant IN ('5','6','E','F') THEN
3708 g_ghr_cpdf_temp.spcl_pay_tbl_type := l_calc_pay_table_id;
3709 END IF;
3710 ELSE
3711 IF g_ghr_cpdf_temp.to_pay_rate_determinant IN ('5','6') AND
3712 pp_gs_equivalent(g_to_pay_plan) THEN
3713 g_ghr_cpdf_temp.spcl_pay_tbl_type := g_pay_table_name;
3714 ELSIF g_ghr_cpdf_temp.to_pay_rate_determinant in ('E','F') AND
3715 pp_gs_equivalent(g_ghr_cpdf_temp.retained_pay_plan) THEN
3716 g_ghr_cpdf_temp.spcl_pay_tbl_type := nvl(g_retained_pay_table_name,'0000');
3717 END IF;
3718 END IF;
3719 -- FWFA Change
3720
3721
3722 --Bug# 6158983
3723 FOR nte_date_rec in nte_date
3724 LOOP
3725 g_ghr_cpdf_temp.appointment_nte_date := fnd_date.canonical_to_date(nte_date_rec.aei_information4);
3726 END LOOP;
3727 -- Bug# 6158983
3728
3729 --Start of BUG# 6631879
3730 IF g_ghr_cpdf_temp.to_work_schedule in ('I','J') then
3731 g_ghr_cpdf_temp.part_time_hours := NULL;
3732 ELSIF g_ghr_cpdf_temp.to_work_schedule in ('F','G','B') then
3733 --Bug# 15941036 added Retirement codes MR
3734 IF g_ghr_cpdf_temp.retirement_plan in ('E','M','MR','T') then
3735 g_ghr_cpdf_temp.part_time_hours := 144;
3736 ELSE
3737 g_ghr_cpdf_temp.part_time_hours := 80;
3738 END IF;
3739 END IF;
3740 --End of BUG# 6631879
3741
3742 EXCEPTION
3743 WHEN OTHERS THEN
3744 FOR per_details in cur_per_details(g_person_id)
3745 LOOP
3746 l_full_name := per_details.name;
3747 l_ssn := per_details.ssn;
3748 l_employee_number := per_details.employee_number;
3749 --Bug # 9329643 Modified SSN to Emp No
3750 l_log_text := 'Error in fetching data for Employee : ' ||l_full_name||
3751 ' Emp No : '||l_employee_number||
3752 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3753 END LOOP;
3754 Raise CPDF_STATRPT_ERROR;
3755
3756 END;
3757 --
3758 -- END of handling exceptions for Bug 3671043
3759 --
3760 calc_is_foreign_duty_station(p_report_date => g_report_date);
3761 insert_row;
3762
3763 <<end_asg_loop>>
3764 NULL;
3765
3766 EXCEPTION
3767 WHEN CPDF_STATRPT_ERROR THEN
3768 hr_utility.set_location('Inside EHRI_STATRPT_ERROR exception ',30);
3769 ghr_mto_int.log_message(p_procedure => g_message_name, --Bug# 4753092
3770 p_message => l_log_text);
3771 --dbms_output.put_line('Name '||l_full_name);
3772 --dbms_output.put_line('Name '||l_log_text);
3773 COMMIT;
3774 END;
3775 END LOOP;
3776
3777 IF NOT l_records_found THEN
3778 l_message_name:='RECORDS_NOT_FOUND';
3779 l_log_text:= 'No Records found for the given Report Date '||g_report_date;
3780 ghr_mto_int.log_message(p_procedure => l_message_name,
3781 p_message => l_log_text
3782 );
3783
3784 l_mesgbuff1:='No Records found for the given Report Date '||g_report_date;
3785 fnd_file.put(fnd_file.log,l_mesgbuff1);
3786 fnd_file.new_line(fnd_file.log);
3787 END IF;
3788
3789 -- purge per design doc
3790 purge_suppression;
3791
3792 END populate_ghr_cpdf_temp;
3793
3794 PROCEDURE ehri_status_main
3795 (errbuf OUT NOCOPY VARCHAR2
3796 ,retcode OUT NOCOPY NUMBER
3797 ,p_report_name IN VARCHAR2
3798 ,p_agency_code IN VARCHAR2
3799 ,p_agency_subelement IN VARCHAR2
3800 -- 8486208 Added new parameter
3801 ,p_agency_group IN VARCHAR2
3802 ,p_report_date IN VARCHAR2
3803 ,p_gen_xml_file IN VARCHAR2 DEFAULT 'N'
3804 ,p_gen_txt_file IN VARCHAR2 DEFAULT 'Y'
3805 )
3806 IS
3807 l_ascii_fname varchar2(80);
3808 l_xml_fname varchar2(80);
3809 l_count_only BOOLEAN;
3810 l_file_name VARCHAR2(500);
3811 l_report_date DATE;
3812 l_ret_code NUMBER;
3813 l_invalid_filename EXCEPTION;
3814 l_report_name VARCHAR2(500);
3815 l_log_text ghr_process_log.log_text%type;
3816 l_message_name ghr_process_log.message_name%type;
3817 l_agency_subelement VARCHAR2(30);
3818 --l_sue_date varchar2(50);
3819
3820 --
3821 BEGIN
3822
3823 ghr_mto_int.set_log_program_name('GHR_CPDF_EHRI_STATRPT');
3824 l_report_date := fnd_date.canonical_to_date(p_report_date);
3825 --l_report_date := sysdate;
3826 --dbms_output.enable(100000000);
3827 --dbms_output.put_line(p_agency_code||' '||p_agency_subelement||' '||l_report_date);
3828 --8486208 added the following condition
3829 IF p_agency_code is NOT NULL OR p_agency_group is NULL THEN
3830 IF p_agency_subelement IS NULL THEN
3831 l_agency_subelement := '%';
3832 ELSE
3833 l_agency_subelement := p_agency_subelement;
3834 END IF;
3835 END IF;
3836
3837 l_report_name := p_report_name;
3838 l_ret_code := 0;
3839
3840 INSERT INTO fnd_sessions
3841 (session_id
3842 ,effective_date)
3843 VALUES
3844 (userenv('sessionid')
3845 ,l_report_date);
3846
3847 --8486208 added the the new parameter
3848 --
3849 populate_ghr_cpdf_temp(p_agency_code||l_agency_subelement,p_agency_group,l_report_date,l_count_only);
3850
3851 -- Generate ASCII and XML files
3852
3853
3854 WritetoFile(l_report_name,p_gen_xml_file,p_gen_txt_file);
3855
3856 -- Purge the table contents after reporting
3857 cleanup_table;
3858 DELETE FROM fnd_sessions
3859 WHERE session_id = userenv('sessionid');
3860
3861
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 l_message_name := 'Unhandled Error';
3865 l_log_text := 'Unhandled Error under procedure ehri_status_main Date '||p_report_date||
3866 ' ** Error Message ** : ' ||substr(sqlerrm,1,1000);
3867 ghr_mto_int.log_message(p_procedure => l_message_name,
3868 p_message => l_log_text);
3869 --dbms_output.put_line(l_log_text);
3870 COMMIT;
3871
3872 END ehri_status_main;
3873
3874 END ghr_cpdf_ehris;