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