[Home] [Help]
PACKAGE BODY: APPS.GHR_EEOC_STATUS_REPORT
Source
1 PACKAGE BODY GHR_EEOC_STATUS_REPORT AS
2 /* $Header: ghreeocs.pkb 120.8.12020000.2 2012/07/05 14:53:03 amnaraya ship $ */
3
4
5 ---------------------------------------------------------------------------------------------
6 -- This is the procedure to populate values into the temporary table GHR_CPDF_TEMP
7 ---------------------------------------------------------------------------------------------
8 PROCEDURE populate_temp (p_business_group IN VARCHAR2
9 ,p_report_date IN DATE
10 ,p_agency_code IN VARCHAR2
11 ,p_agency_sub_code IN VARCHAR2) IS
12
13 l_proc VARCHAR2(30) := 'populate_temp';
14 l_peopei_data PER_PEOPLE_EXTRA_INFO%ROWTYPE;
15 l_grade_id NUMBER;
16 l_full_name PER_ALL_PEOPLE.FULL_NAME%TYPE;
17 l_emp_no PER_ALL_PEOPLE.EMPLOYEE_NUMBER%TYPE;
18 l_log_text VARCHAR2(2000);
19 l_records_found BOOLEAN := FALSE;
20 l_mesgbuff1 VARCHAR2(4000);
21
22
23 --BUG # 13555003 modified the cursor to fetch active employee in the
24 -- reporting year. As it will fetch multiple records for the same person
25 -- modified to fetch the latest record with in the condition
26 cursor get_active_assignments
27 is
28 SELECT asg.assignment_id,
29 asg.person_id,
30 asg.position_id,
31 asg.grade_id,
32 asg.job_id,
33 asg.location_id,
34 asg.effective_start_date,
35 asg.business_group_id,
36 ast.per_system_status assignment_status_type,
37 ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) agency_code
38 FROM PER_ALL_ASSIGNMENTS_F asg, PER_ASSIGNMENT_STATUS_TYPES ast
39 WHERE ast.assignment_status_type_id = asg.assignment_status_type_id
40 AND p_report_date > asg.effective_start_date
41 AND TO_CHAR(asg.effective_end_date,'YYYY') >= to_char(p_report_date,'YYYY')
42 AND ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
43 AND asg.assignment_type <> 'B'
44 AND asg.position_id IS NOT NULL
45 AND ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) like p_agency_code||NVL(p_agency_sub_code,'%')
46 AND decode(hr_general.get_xbg_profile,'Y',asg.business_group_id , p_business_group) = asg.business_group_id
47 AND effective_start_date = (select max(effective_start_date) from PER_ALL_ASSIGNMENTS_F asg1, PER_ASSIGNMENT_STATUS_TYPES ast1
48 WHERE asg1.assignment_id = asg.assignment_id
49 AND ast1.assignment_status_type_id = asg1.assignment_status_type_id
50 AND p_report_date > asg1.effective_start_date
51 AND TO_CHAR(asg1.effective_end_date,'YYYY') >= to_char(p_report_date,'YYYY')
52 AND ast1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
53 AND asg1.assignment_type <> 'B'
54 AND asg1.position_id IS NOT NULL
55 AND ghr_api.get_position_agency_code_pos(asg1.position_id,asg1.business_group_id) like p_agency_code||NVL(p_agency_sub_code,'%'))
56 ORDER BY assignment_id;
57
58 BEGIN
59
60 G_TEMP_REC := NULL;
61 cleanup_table(p_type => 'EEOCSTATUS');
62 FOR l_ass_rec IN get_active_assignments
63 LOOP
64 BEGIN
65 g_temp_rec := NULL;
66 g_message_name := NULL;
67 l_records_found := TRUE;
68 g_temp_rec.agency_code := l_ass_rec.agency_code;
69 l_grade_id := l_ass_rec.grade_id;
70
71 --BUG # 13455262 Modified to fetch appointment date for a person
72 get_appointment_date(p_person_id => l_ass_rec.person_id,
73 p_report_date => p_report_date,
74 p_appointment_date => g_appointment_date);
75
76
77 BEGIN
78 FETCH_PEOPLE_DETAILS(p_person_id => l_ass_rec.person_id,
79 p_report_date => p_report_date,
80 p_full_name => l_full_name,
81 p_employee_no => l_emp_no);
82 EXCEPTION
83 WHEN OTHERS THEN
84 l_log_text := 'Error in fetching data of the person: Person Id ' ||l_ass_rec.person_id||
85 ' Assignment Id: '||l_ass_rec.assignment_id||'; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
86 Raise EEOC_STATUS_RPT_ERR;
87 END;
88
89 BEGIN
90 g_message_name := 'Occupational Series';
91
92 g_temp_rec.to_occ_code := GHR_API.GET_JOB_OCC_SERIES_JOB(p_job_id => l_ass_rec.job_id,
93 p_business_group_id => p_business_group);
94
95 FETCH_ASGEI_DETAILS(p_assignment_id => l_ass_rec.assignment_id,
96 p_report_date => p_report_date);
97
98 FETCH_GRADE_DETAILS(p_grade_id => l_ass_rec.grade_id);
99
100 FETCH_PEREI_DETAILS(p_person_id => l_ass_rec.person_id,
101 p_report_date => p_report_date);
102
103 FETCH_POSEI_DETAILS(p_position_id => l_ass_rec.position_id,
104 p_report_date => p_report_date);
105
106
107
108
109 g_message_name := 'Special Info: Education Dtls';
110 ghr_api.return_education_details(p_person_id => l_ass_rec.person_id,
111 p_effective_date => p_report_date,
112 p_education_level => g_temp_rec.education_level,
113 p_academic_discipline => g_temp_rec.academic_discipline,
114 p_year_degree_attained => g_temp_rec.year_degree_attained);
115
116 FETCH_ELEMENT_DETAILS(p_assignment_id => l_ass_rec.assignment_id,
117 p_report_date => p_report_date,
118 p_business_group => p_business_group);
119
120
121 EXCEPTION
122 WHEN EEOC_STATUS_RPT_ERR THEN
123 Raise;
124 WHEN OTHERS THEN
125 l_log_text := 'Error in fetching data for Employee : ' ||l_full_name||
126 ' Emp No : '||l_emp_no||'; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
127 Raise EEOC_STATUS_RPT_ERR;
128
129 END;
130
131
132 insert_row;
133 EXCEPTION
134 WHEN EEOC_STATUS_RPT_ERR THEN
135 hr_utility.set_location('Inside EEOC_STATUS_RPT_ERR exception ',30);
136 ghr_mto_int.log_message(p_procedure => g_message_name,
137 p_message => l_log_text);
138 COMMIT;
139 END;
140 END LOOP;
141
142
143 IF NOT l_records_found THEN
144 g_message_name:='RECORDS_NOT_FOUND';
145 l_log_text:= 'No Records found for the given Report Date '||p_report_date;
146 ghr_mto_int.log_message(p_procedure => g_message_name,
147 p_message => l_log_text
148 );
149
150 l_mesgbuff1:='No Records found for the given Report Date '||p_report_date;
151 fnd_file.put(fnd_file.log,l_mesgbuff1);
152 fnd_file.new_line(fnd_file.log);
153 END IF;
154
155 END populate_temp;
156
157
158 PROCEDURE FETCH_PEOPLE_DETAILS(p_person_id IN NUMBER,
159 p_report_date IN DATE,
160 p_full_name OUT NOCOPY VARCHAR2,
161 p_employee_no OUT NOCOPY VARCHAR2
162 ) IS
163
164 l_proc varchar2(30) := 'fetch_people_details';
165 l_full_name PER_ALL_PEOPLE.FULL_NAME%TYPE;
166 l_employee_number PER_ALL_PEOPLE.EMPLOYEE_NUMBER%TYPE;
167
168 CURSOR c_per_det
169 IS
170 SELECT pap.sex,
171 pap.date_of_birth,
172 pap.full_name,
173 pap.employee_number
174 FROM per_all_people pap
175 WHERE p_person_id = pap.person_id
176 AND p_report_date between pap.effective_start_date AND pap.effective_end_date;
177
178 BEGIN
179 hr_utility.set_location('Entering:'||l_proc,5);
180 g_message_name := 'Fetch Person Details';
181
182 FOR per_rec in c_per_det
183 LOOP
184 g_temp_rec.sex := per_rec.sex;
185 g_temp_rec.employee_date_of_birth := per_rec.date_of_birth;
186 l_full_name := per_rec.full_name;
187 l_employee_number := per_rec.employee_number;
188 -- Bug # 12865107 Reporting Employee Number instead of Employee Id
189 -- as employee_number field not available in GHR_CPDF_TEMP using from national_identifier
190 g_temp_rec.from_national_identifier := l_employee_number;
191 END LOOP;
192
193 p_full_name := l_full_name;
194 p_employee_no := l_employee_number;
195 END;
196
197
198 PROCEDURE FETCH_GRADE_DETAILS(p_grade_id in NUMBER) IS
199
200 l_proc varchar2(30) := 'fetch_grade_details';
201
202 CURSOR c_grade_def
203 IS
204 SELECT segment1,
205 segment2
206 FROM PER_GRADE_DEFINITIONS
207 WHERE grade_definition_id = (SELECT MAX(grade_definition_id)
208 FROM per_grades
209 WHERE grade_id = p_grade_id);
210 BEGIN
211 hr_utility.set_location('Entering:'||l_proc,5);
212 g_message_name := 'Grade Details';
213
214 FOR l_grade_rec IN c_grade_def
215 LOOP
216 g_temp_rec.to_pay_plan := substr(l_grade_rec.segment1,1,2);
217 g_temp_rec.to_grade_or_level := substr(l_grade_rec.segment2,1,2);
218 END LOOP;
219 END;
220
221
222
223 PROCEDURE FETCH_PEREI_DETAILS(p_person_id in NUMBER,
224 p_report_date in DATE) IS
225 l_proc VARCHAR2(30) := 'fetch_perei_details';
226 l_perei_data PER_PEOPLE_EXTRA_INFO%ROWTYPE;
227 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
228 BEGIN
229 hr_utility.set_location('Entering:'||l_proc,5);
230
231 g_message_name := 'Person EIT: Person Group1';
232 l_perei_data := null;
233 ghr_history_fetch.fetch_peopleei(p_person_id => p_person_id,
234 p_information_type => 'GHR_US_PER_GROUP1',
235 p_date_effective => p_report_date,
236 p_per_ei_data => l_perei_data
237 );
238 g_temp_rec.ehri_employee_id := to_number(l_perei_data.PEI_INFORMATION18);
239 g_temp_rec.appoint_type_code := l_perei_data.PEI_INFORMATION3;
240 g_temp_rec.handicap_code := l_perei_data.PEI_INFORMATION11;
241
242 --Bug #13455262 modified to compare appointment date instead of report date
243 IF g_appointment_date < TO_DATE('01/07/2006','DD/MM/RRRR') THEN
244 g_temp_rec.race_national_origin := l_perei_data.PEI_INFORMATION5;
245 ELSE
246 g_temp_rec.race_national_origin := NULL;
247 END IF;
248
249
250 g_message_name := 'Person EIT: Person SCD Info';
251 l_perei_data := null;
252 ghr_history_fetch.fetch_peopleei(p_person_id => p_person_id,
253 p_information_type => 'GHR_US_PER_SCD_INFORMATION',
254 p_date_effective => p_report_date,
255 p_per_ei_data => l_perei_data
256 );
257 g_temp_rec.service_comp_date := fnd_date.canonical_to_date(l_perei_data.pei_information3);
258
259 IF p_report_date >= TO_DATE('01/07/2006','DD/MM/RRRR') THEN
260
261 g_message_name := 'Person EIT: Ethnicity, Race';
262 l_perei_data := null;
263 ghr_history_fetch.fetch_peopleei (p_person_id => p_person_id,
264 p_information_type => 'GHR_US_PER_ETHNICITY_RACE',
265 p_date_effective => p_report_date,
266 p_per_ei_data => l_perei_data
267 );
268
269 --Populate Race only if atleast one data segment is entered.
270 IF l_perei_data.pei_information3 IS NOT NULL OR
271 l_perei_data.pei_information4 IS NOT NULL OR
272 l_perei_data.pei_information5 IS NOT NULL OR
273 l_perei_data.pei_information6 IS NOT NULL OR
274 l_perei_data.pei_information7 IS NOT NULL OR
275 l_perei_data.pei_information8 IS NOT NULL THEN
276 g_temp_rec.race_ethnic_info := NVL(l_perei_data.pei_information3,'0') || NVL(l_perei_data.pei_information4,'0') || NVL(l_perei_data.pei_information5,'0') ||
277 NVL(l_perei_data.pei_information6,'0') || NVL(l_perei_data.pei_information7,'0') || NVL(l_perei_data.pei_information8,'0');
278 END IF;
279 ELSE
280 g_temp_rec.race_ethnic_info := NULL;
281 END IF;
282
283 IF g_temp_rec.to_pay_rate_determinant in ('A','B','E','F','U','V') THEN
284 g_message_name := 'Person EIT: Retained Grade';
285
286 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details(p_person_id => p_person_id,
287 p_effective_date => p_report_date);
288
289 g_temp_rec.to_pay_basis := l_retained_grade_rec.pay_basis;
290 --Bug # 12866172
291 g_temp_rec.to_pay_plan := l_retained_grade_rec.pay_plan;
292 g_temp_rec.to_grade_or_level := l_retained_grade_rec.grade_or_level;
293 g_temp_rec.to_step_or_rate := l_retained_grade_rec.step_or_rate;
294 END IF;
295
296 END;
297
298 PROCEDURE FETCH_POSEI_DETAILS(p_position_id IN NUMBER,
299 p_report_date IN DATE) IS
300 l_proc VARCHAR2(30) := 'fetch_posei_details';
301 l_posei_data PER_POSITION_EXTRA_INFO%ROWTYPE;
302 BEGIN
303 hr_utility.set_location('Entering:'||l_proc,5);
304 g_message_name := 'Position EIT: Position Group1';
305 l_posei_data := NULL;
306 ghr_history_fetch.fetch_positionei(p_position_id => p_position_id,
307 p_information_type => 'GHR_US_POS_GRP1',
308 p_date_effective => p_report_date,
309 p_pos_ei_data => l_posei_data
310 );
311 g_temp_rec.supervisory_status := l_posei_data.poei_information16;
312
313 IF g_temp_rec.to_pay_basis is NULL THEN
314 g_message_name := 'Position EIT: Valid Grade';
315 l_posei_data := NULL;
316 ghr_history_fetch.fetch_positionei(p_position_id => p_position_id,
317 p_information_type => 'GHR_US_POS_VALID_GRADE',
318 p_date_effective => p_report_date,
319 p_pos_ei_data => l_posei_data
320 );
321 g_temp_rec.to_pay_basis := l_posei_data.poei_information6;
322 END IF;
323
324 g_message_name := 'Position EIT: Position Group2';
325 l_posei_data := NULL;
326 ghr_history_fetch.fetch_positionei(p_position_id => p_position_id,
327 p_information_type => 'GHR_US_POS_GRP2',
328 p_date_effective => p_report_date,
329 p_pos_ei_data => l_posei_data);
330 hr_utility.set_location('mani l_posei_data.poei_information17'||l_posei_data.poei_information17,100);
331 IF NVL(l_posei_data.poei_information17,'@') = 'APPR' THEN
332 g_temp_rec.cont_pay_type_code := 'APF';
333 ELSE
334 g_temp_rec.cont_pay_type_code := 'NAF';
335 END IF;
336 hr_utility.set_location('mani g_temp_rec.cont_pay_type_code'||g_temp_rec.cont_pay_type_code,100);
337
338 END FETCH_POSEI_DETAILS;
339
340 PROCEDURE FETCH_ASGEI_DETAILS(p_assignment_id in NUMBER,
341 p_report_date in DATE) IS
342
343 l_proc VARCHAR2(30) := 'fetch_asgei_details';
344 l_asgnei_data PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
345 BEGIN
346 hr_utility.set_location('Entering:'||l_proc,5);
347
348 g_message_name := 'Assignment EIT: Asg SF52';
349 l_asgnei_data := NULL;
350 GHR_HISTORY_FETCH.fetch_asgei(p_assignment_id => p_assignment_id,
351 p_information_type => 'GHR_US_ASG_SF52',
352 p_date_effective => p_report_date,
353 p_asg_ei_data => l_asgnei_data);
354
355 g_temp_rec.tenure := l_asgnei_data.aei_information4;
356 g_temp_rec.to_step_or_rate := l_asgnei_data.aei_information3;
357 g_temp_rec.to_pay_rate_determinant := l_asgnei_data.aei_information6;
358
359 l_asgnei_data := NULL;
360 GHR_HISTORY_FETCH.fetch_asgei(p_assignment_id => p_assignment_id,
361 p_information_type => 'GHR_US_ASG_NON_SF52',
362 p_date_effective => p_report_date,
363 p_asg_ei_data => l_asgnei_data);
364 g_temp_rec.pay_status := l_asgnei_data.aei_information14;
365
366 END FETCH_ASGEI_DETAILS;
367
368 PROCEDURE FETCH_ELEMENT_DETAILS(p_assignment_id IN NUMBER,
369 p_report_date IN DATE,
370 p_business_group IN NUMBER) IS
371 l_proc varchar2(30) := 'Fetch_Element_Details';
372 l_value VARCHAR2(250);
373 l_effective_start_date DATE;
374 BEGIN
375 hr_utility.set_location('Entering:'||l_proc,5);
376
377 g_message_name := 'Fetch Element: Basic Salary';
378 l_value := NULL;
379 ghr_per_sum.get_element_details (p_element_name => 'Basic Salary Rate'
380 ,p_input_value_name => 'Rate'
381 ,p_assignment_id => p_assignment_id
382 ,p_effective_date => p_report_date
383 ,p_value => l_value
384 ,p_effective_start_date => l_effective_start_date
385 ,p_business_group_id => p_business_group);
386
387 g_temp_rec.to_basic_pay := to_number(l_value);
388
389 g_message_name := 'Fetch Element: Adj Basic Pay';
390 l_value := NULL;
391 ghr_per_sum.get_element_details (p_element_name => 'Adjusted Basic Pay'
392 ,p_input_value_name => 'Amount'
393 ,p_assignment_id => p_assignment_id
394 ,p_effective_date => p_report_date
395 ,p_value => l_value
396 ,p_effective_start_date => l_effective_start_date
397 ,p_business_group_id => p_business_group);
398
399 g_temp_rec.to_adj_basic_pay := to_number(l_value);
400
401 g_message_name := 'Fetch Element: Total Pay';
402 l_value := NULL;
403 ghr_per_sum.get_element_details (p_element_name => 'Total Pay'
404 ,p_input_value_name => 'Amount'
405 ,p_assignment_id => p_assignment_id
406 ,p_effective_date => p_report_date
407 ,p_value => l_value
408 ,p_effective_start_date => l_effective_start_date
409 ,p_business_group_id => P_business_group);
410
411 g_temp_rec.to_total_salary := to_number(l_value);
412
413 END;
414
415
416 --- BUG # 13455262 Added to fetch the latest appointmnet date
417 PROCEDURE get_appointment_date(p_person_id IN NUMBER
418 ,p_report_date IN DATE
419 ,p_appointment_date OUT NOCOPY DATE) IS
420
421 CURSOR cur_per IS
422 SELECT DECODE(PER.CURRENT_EMPLOYEE_FLAG, 'Y', PPS.DATE_START, DECODE(PER.CURRENT_NPW_FLAG, 'Y', PPP.DATE_START, NULL)) hire_date
423 FROM per_all_people PER ,
424 PER_PERIODS_OF_SERVICE PPS ,
425 PER_PERIODS_OF_PLACEMENT PPP
426 WHERE PPS.PERSON_ID (+) = PER.PERSON_ID AND
427 PPP.PERSON_ID (+) = PER.PERSON_ID AND
428 PER.PERSON_ID = p_person_id AND
429 P_REPORT_DATE BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND
430 ((PER.EMPLOYEE_NUMBER IS NULL) OR
431 (PER.EMPLOYEE_NUMBER IS NOT NULL AND
432 PPS.DATE_START = (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID = PER.PERSON_ID AND
433 PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND
434 ((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL AND
435 PPP.DATE_START = (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND
436 PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)));
437
438 --
439 BEGIN
440
441 FOR cur_per_rec IN cur_per LOOP
442 p_appointment_date := cur_per_rec.hire_date;
443 END LOOP;
444
445 END get_appointment_date;
446
447 PROCEDURE cleanup_table(p_type in VARCHAR2) IS
448 l_proc varchar2(30) := 'cleanup_table';
449 BEGIN
450 hr_utility.set_location('Entering:'||l_proc,5);
451 DELETE FROM ghr_cpdf_temp
452 WHERE report_type = p_type
453 AND session_id = userenv('SESSIONID');
454 END cleanup_table;
455
456 PROCEDURE Insert_Row IS
457 l_proc varchar2(30) := 'insert_row';
458 l_log_text ghr_process_log.log_text%type;
459 l_message_name ghr_process_log.message_name%type;
460
461 BEGIN
462 hr_utility.set_location('Entering:'||l_proc,5);
463
464 INSERT INTO ghr_cpdf_temp(report_type
465 ,session_id
466 ,agency_code
467 ,bargaining_unit_status
468 ,education_level
469 ,effective_date
470 ,employee_date_of_birth
471 ,handicap_code
472 ,pay_status
473 ,race_national_origin
474 ,service_comp_date
475 ,sex
476 ,supervisory_status
477 ,tenure
478 ,to_basic_pay
479 ,to_grade_or_level
480 ,to_occ_code
481 ,to_pay_basis
482 ,to_pay_plan
483 ,to_step_or_rate
484 ,to_total_salary
485 ,from_national_identifier
486 ,ehri_employee_id
487 ,appoint_type_code
488 ,to_adj_basic_pay
489 ,appropriation_code
490 ,race_ethnic_info
491 ,cont_pay_type_code)
492 values (
493 'EEOCSTATUS'
494 ,userenv('SESSIONID')
495 ,g_temp_rec.agency_code
496 ,g_temp_rec.bargaining_unit_status
497 ,g_temp_rec.education_level
498 ,g_temp_rec.effective_date
499 ,g_temp_rec.employee_date_of_birth
500 ,g_temp_rec.handicap_code
501 ,g_temp_rec.pay_status
502 ,g_temp_rec.race_national_origin
503 ,g_temp_rec.service_comp_date
504 ,g_temp_rec.sex
505 ,g_temp_rec.supervisory_status
506 ,g_temp_rec.tenure
507 ,g_temp_rec.to_basic_pay
508 ,g_temp_rec.to_grade_or_level
509 ,g_temp_rec.to_occ_code
510 ,g_temp_rec.to_pay_basis
511 ,g_temp_rec.to_pay_plan
512 ,g_temp_rec.to_step_or_rate
513 ,g_temp_rec.to_total_salary
514 ,g_temp_rec.from_national_identifier
515 ,g_temp_rec.ehri_employee_id
516 ,g_temp_rec.appoint_type_code
517 ,g_temp_rec.to_adj_basic_pay
518 ,g_temp_rec.appropriation_code
519 ,g_temp_rec.race_ethnic_info
520 ,g_temp_rec.cont_pay_type_code);
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 g_message_name := 'Unhandled Error';
525 l_log_text := 'Unhandled Error under procedure insert_row'||
526 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
527 ghr_mto_int.log_message(p_procedure => g_message_name,
528 p_message => l_log_text);
529 COMMIT;
530
531 END insert_row;
532
533 ---------------------------------------------------------------------------
534 --- THIS IS PROC TO GENERATE THE ASCII and XML file
535 ---------------------------------------------------------------------------
536 PROCEDURE WritetoFile(p_input_file_name IN VARCHAR2
537 ,p_gen_file IN VARCHAR2
538 ,p_report_date IN DATE
539 ,p_sub_agency_subelement IN VARCHAR2)
540 IS
541 p_xml_fp UTL_FILE.FILE_TYPE;
542 p_ascii_fp UTL_FILE.FILE_TYPE;
543 l_audit_log_dir varchar2(500);
544 l_xml_file_name varchar2(500);
545 l_ascii_file_name varchar2(500);
546 l_output_xml_fname varchar2(500);
547 l_output_ascii_fname varchar2(500);
548 v_tags t_tags;
549 l_count NUMBER;
550 l_session_id NUMBER;
551 l_request_id NUMBER;
552 l_temp VARCHAR2(500);
553
554 CURSOR c_eeoc_status(c_session_id NUMBER)
555 IS
556 SELECT *
557 FROM GHR_CPDF_TEMP
558 WHERE SESSION_ID = c_session_id
559 AND REPORT_TYPE = 'EEOCSTATUS';
560
561 CURSOR c_get_dir
562 IS
563 SELECT value
564 FROM V$PARAMETER
565 WHERE NAME = 'utl_file_dir';
566
567 BEGIN
568
569 -- Assigning the File name.
570 l_xml_file_name := p_input_file_name || '.xml';
571 l_ascii_file_name := p_input_file_name || '.txt';
572 l_count := 1;
573 l_session_id := USERENV('SESSIONID');
574
575 FOR rec IN c_get_dir
576 LOOP
577 l_audit_log_dir := rec.value;
578 END LOOP;
579
580
581 -- Check whether more than one util file directory is found
582 IF INSTR(l_audit_log_dir,',') > 0 THEN
583 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
584 END IF;
585
586
587 -- Find out whether the OS is MS or Unix/Linux based
588 -- If it's greater than 0, it's Unix/Linux based environment
589 IF INSTR(l_audit_log_dir,'/') > 0 THEN
590 l_output_xml_fname := l_audit_log_dir || '/' || l_xml_file_name;
591 l_output_ascii_fname := l_audit_log_dir || '/' || l_ascii_file_name;
592 ELSE
593 l_output_xml_fname := l_audit_log_dir || '\' || l_xml_file_name;
594 l_output_ascii_fname := l_audit_log_dir || '\' || l_ascii_file_name;
595 END IF;
596
597 IF p_gen_file = 'XML' THEN
598 p_xml_fp := utl_file.fopen(l_audit_log_dir,l_xml_file_name,'w',32767);
599 utl_file.put_line(p_xml_fp,'<?xml version="1.0" encoding="UTF-8"?>');
600 -- Writing from and to dates
601 utl_file.put_line(p_xml_fp,'<Records>');
602 -- Loop through cursor and write the values into the XML and ASCII File.
603 FOR ctr_table IN c_eeoc_status(l_session_id) LOOP
604 WriteTagValues(ctr_table,v_tags,p_report_date,p_sub_agency_subelement);
605 utl_file.put_line(p_xml_fp,'<Record' || l_count || '>');
606 WriteXMLvalues(p_xml_fp,v_tags);
607 utl_file.put_line(p_xml_fp,'</Record' || l_count || '>');
608 IF l_count = 1 THEN
609 WriteHeaderRow(p_ascii_fp,v_tags,p_gen_file);
610 END IF;
611 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_file);
612 l_count := l_count + 1;
613 END LOOP;
614 utl_file.put_line(p_xml_fp,'</Records>');
615 utl_file.fclose(p_xml_fp);
616 ELSE
617 p_ascii_fp := utl_file.fopen(l_audit_log_dir,l_ascii_file_name,'w',32767);
618 FOR ctr_table IN c_eeoc_status(l_session_id) LOOP
619 WriteTagValues(ctr_table,v_tags,p_report_date,p_sub_agency_subelement);
620 IF l_count = 1 THEN
621 WriteHeaderRow(p_ascii_fp,v_tags,p_gen_file);
622 END IF;
623 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_file);
624 l_count := l_count + 1;
625 END LOOP;
626 END IF;
627
628 l_count := l_count - 1;
629 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
630 fnd_file.put_line(fnd_file.log,'Total Records : ' || l_count );
631 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
632 -- Write the end tag and close the XML File.
633
634 fnd_file.put_line(fnd_file.log,'------------Path of output file----------------');
635 IF p_gen_file = 'XML' THEN
636 fnd_file.put_line(fnd_file.log,'XML file : ' || l_output_xml_fname);
637 END IF;
638 IF p_gen_file = 'TEXT' THEN
639 fnd_file.put_line(fnd_file.log,'Text file : ' || l_output_ascii_fname);
640 END IF;
641 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
642
643 END WritetoFile;
644
645 PROCEDURE WriteTagValues(p_eeoc_rec GHR_CPDF_TEMP%ROWTYPE,
646 p_tags OUT NOCOPY t_tags,
647 p_report_date IN DATE,
648 p_sub_agency_subelement IN VARCHAR2)
649 IS
650 l_count NUMBER;
651 CURSOR chk_gs_equivalent
652 IS
653 SELECT 1
654 FROM GHR_PAY_PLANS
655 WHERE PAY_PLAN = p_eeoc_rec.to_pay_plan
656 AND EQUIVALENT_PAY_PLAN = 'GS';
657
658 CURSOR calc_age
659 IS
660 SELECT TO_CHAR(TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE),p_eeoc_rec.employee_date_of_birth)/12)) age
661 FROM DUAL;
662
663 BEGIN
664 l_count := 1;
665 -- Writing to Tags
666 p_tags(l_count).tagname := 'DATE_SUBMITTED';
667 p_tags(l_count).tagvalue := TO_CHAR(trunc(sysdate), 'YYYYMMDD');
668 l_count := l_count+1;
669
670 p_tags(l_count).tagname := 'REPORT_FY';
671 p_tags(l_count).tagvalue := TO_CHAR(p_report_date,'YYYY');
672 l_count := l_count+1;
673
674 -- Check this
675 p_tags(l_count).tagname := 'AGENCY_FIRES';
676 p_tags(l_count).tagvalue := p_sub_agency_subelement;
677 l_count := l_count+1;
678
679 --Bug # 12865107 Added Employee Number
680 p_tags(l_count).tagname := 'EMPLOYEE_UNIQUE_ID';
681 IF p_eeoc_rec.ehri_employee_id is NOT NULL then
682 p_tags(l_count).tagvalue := p_eeoc_rec.ehri_employee_id ;
683 ELSE
684 p_tags(l_count).tagvalue := p_eeoc_rec.from_national_identifier ;
685 END IF;
686 l_count := l_count+1;
687
688 p_tags(l_count).tagname := 'EMP_AGENCY_CPDF';
689 p_tags(l_count).tagvalue := p_eeoc_rec.agency_code;
690 l_count := l_count+1;
691
692 p_tags(l_count).tagname := 'OCCUPATION';
693 p_tags(l_count).tagvalue := p_eeoc_rec.to_occ_code;
694 l_count := l_count+1;
695
696 p_tags(l_count).tagname := 'GRADE';
697 p_tags(l_count).tagvalue := p_eeoc_rec.to_grade_or_level;
698 l_count := l_count+1;
699
700 p_tags(l_count).tagname := 'PAY_PLAN';
701 p_tags(l_count).tagvalue := p_eeoc_rec.to_pay_plan;
702 l_count := l_count+1;
703
704 ---Bug # 12865323 Added DD as per the bug
705 p_tags(l_count).tagname := 'SERVICE_COMP_DATE';
706 p_tags(l_count).tagvalue := TO_CHAR(p_eeoc_rec.service_comp_date,'YYYYMMDD');
707 l_count := l_count+1;
708
709 p_tags(l_count).tagname := 'SEX';
710 p_tags(l_count).tagvalue := NVL(p_eeoc_rec.sex,'*');
711 l_count := l_count+1;
712
713 p_tags(l_count).tagname := 'SUPERVISORY_STATUS';
714 p_tags(l_count).tagvalue := p_eeoc_rec.supervisory_status;
715 l_count := l_count+1;
716
717 p_tags(l_count).tagname := 'TENURE';
718 p_tags(l_count).tagvalue := NVL(p_eeoc_rec.tenure,'*');
719 l_count := l_count+1;
720
721 p_tags(l_count).tagname := 'TYPE_APPOINTMENT';
722 p_tags(l_count).tagvalue := p_eeoc_rec.appoint_type_code;
723 l_count := l_count+1;
724
725 p_tags(l_count).tagname := 'EDUCATION_LVL';
726 p_tags(l_count).tagvalue := p_eeoc_rec.education_level;
727 l_count := l_count+1;
728
729 p_tags(l_count).tagname := 'GS_RELATED_GRADE';
730 p_tags(l_count).tagvalue := NULL;
731 FOR rec in chk_gs_equivalent
732 LOOP
733 p_tags(l_count).tagvalue := p_eeoc_rec.to_grade_or_level;
734 END LOOP;
735 l_count := l_count+1;
736
737 p_tags(l_count).tagname := 'HANDICAP';
738 p_tags(l_count).tagvalue := p_eeoc_rec.handicap_code;
739 l_count := l_count+1;
740
741 p_tags(l_count).tagname := 'STEP_RATE';
742 p_tags(l_count).tagvalue := p_eeoc_rec.to_step_or_rate;
743 l_count := l_count+1;
744
745 p_tags(l_count).tagname := 'SENIOR_PAY_LVL_INDICATOR';
746 --Bug # 12860977 Modified the displaying of Y for pay plans
747 --'EP','ES','FE','FO','FT','FX','IE','IG','IP','SL','SQ','SS','ST'
748 p_tags(l_count).tagvalue := 'N';
749 IF p_eeoc_rec.to_pay_plan IN ('EP','ES','FE','FO','FT','FX',
750 'IE','IG','IP','SL','SQ','SS','ST') THEN
751 p_tags(l_count).tagvalue := 'Y';
752 END IF;
753 l_count := l_count+1;
754
755 p_tags(l_count).tagname := 'ERI_IDENTIFIER';
756 p_tags(l_count).tagvalue := p_eeoc_rec.race_ethnic_info;
757 l_count := l_count+1;
758
759 p_tags(l_count).tagname := 'RACE_NATL_ORIGIN';
760 p_tags(l_count).tagvalue := p_eeoc_rec.race_national_origin;
761 l_count := l_count+1;
762
763 p_tags(l_count).tagname := 'PAY_BASIS';
764 p_tags(l_count).tagvalue := p_eeoc_rec.to_pay_basis;
765 l_count := l_count+1;
766
767 p_tags(l_count).tagname := 'PAY_STATUS';
768 p_tags(l_count).tagvalue := p_eeoc_rec.pay_status;
769 l_count := l_count+1;
770
771 p_tags(l_count).tagname := 'EMPLOYEE_TYPE';
772 p_tags(l_count).tagvalue := p_eeoc_rec.cont_pay_type_code;
773 l_count := l_count+1;
774
775 p_tags(l_count).tagname := 'AGE';
776 IF TRUNC(SYSDATE) > p_eeoc_rec.employee_date_of_birth THEN
777 FOR rec in calc_age
778 LOOP
779 p_tags(l_count).tagvalue := rec.age;
780 END LOOP;
781 ELSE
782 p_tags(l_count).tagvalue := 0;
783 END IF;
784 l_count := l_count+1;
785
786
787 IF p_eeoc_rec.to_pay_basis <> 'PA' THEN
788 --Bug# 12885993 Modified the Format Mask for other than PA Pay Basis
789 p_tags(l_count).tagname := 'BASIC_PAY';
790 p_tags(l_count).tagvalue := replace(ltrim(to_char(p_eeoc_rec.to_basic_pay,'99999999.99')),'.','');
791 l_count := l_count+1;
792
793 p_tags(l_count).tagname := 'ADJUSTED_PAY';
794 p_tags(l_count).tagvalue := replace(ltrim(to_char(p_eeoc_rec.to_adj_basic_pay,'99999999.99')),'.','');
795 l_count := l_count+1;
796
797 p_tags(l_count).tagname := 'TOTAL_SALARY';
798 p_tags(l_count).tagvalue := replace(ltrim(to_char(p_eeoc_rec.to_total_salary,'99999999.99')),'.','');
799 l_count := l_count+1;
800 ELSE
801 p_tags(l_count).tagname := 'BASIC_PAY';
802 p_tags(l_count).tagvalue := trunc(p_eeoc_rec.to_basic_pay);
803 l_count := l_count+1;
804
805 p_tags(l_count).tagname := 'ADJUSTED_PAY';
806 p_tags(l_count).tagvalue := trunc(p_eeoc_rec.to_adj_basic_pay);
807 l_count := l_count+1;
808
809 p_tags(l_count).tagname := 'TOTAL_SALARY';
810 p_tags(l_count).tagvalue := trunc(p_eeoc_rec.to_total_salary);
811 l_count := l_count+1;
812 END IF;
813
814 END WriteTagValues;
815
816 -----------------------------------------------------------------------------
817 -- Writing the records from PL/SQL table p_tags into XML File
818 -----------------------------------------------------------------------------
819 PROCEDURE WriteXMLvalues(p_l_fp utl_file.file_type,
820 p_tags t_tags)
821 IS
822 BEGIN
823 FOR l_tags IN p_tags.FIRST .. p_tags.LAST
824 LOOP
825 utl_file.put_line(p_l_fp,'<' || p_tags(l_tags).tagname || '>' || p_tags(l_tags).tagvalue || '</' || p_tags(l_tags).tagname || '>');
826 END LOOP;
827 END;
828
829 -----------------------------------------------------------------------------
830 -- Writing the records from PL/SQL table p_tags into Text and FND Output File
831 -----------------------------------------------------------------------------
832 PROCEDURE WriteAsciivalues(p_l_fp utl_file.file_type,
833 p_tags t_tags,
834 p_gen_file IN VARCHAR2 )
835 IS
836 l_temp VARCHAR2(4000);
837 l_tot NUMBER;
838 BEGIN
839 l_tot := p_tags.COUNT;
840 IF l_tot > 0 THEN
841 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
842 IF l_tags = l_tot THEN
843 l_temp := p_tags(l_tags).tagvalue;
844 IF p_gen_file = 'TEXT' THEN
845 utl_file.put_line(p_l_fp,l_temp);
846 END IF;
847 fnd_file.put_line(fnd_file.output,l_temp);
848 ELSE
849 l_temp := p_tags(l_tags).tagvalue || '|';
850 IF p_gen_file = 'TEXT' THEN
851 utl_file.put(p_l_fp,l_temp);
852 END IF;
853 fnd_file.put(fnd_file.output,l_temp);
854 END IF;
855 END LOOP;
856 END IF;
857
858 END WriteAsciivalues;
859
860 ----------------------------------------------------------------------------
861 -- Writing the Header into FND Output file
862 -----------------------------------------------------------------------------
863 PROCEDURE WriteHeaderRow(p_l_fp UTL_FILE.FILE_TYPE,
864 p_tags T_TAGS,
865 p_gen_file IN VARCHAR2)
866 IS
867 l_tot NUMBER;
868 l_temp VARCHAR2(1000);
869 BEGIN
870 l_tot := p_tags.COUNT;
871 FOR l_tags IN p_tags.FIRST .. p_tags.LAST
872 LOOP
873 l_temp := p_tags(l_tags).tagname;
874 IF NOT(l_tags = l_tot) THEN
875 l_temp := l_temp||'|';
876 END IF;
877 IF p_gen_file = 'TEXT' THEN
878 utl_file.put(p_l_fp,l_temp);
879 END IF;
880
881 fnd_file.put(fnd_file.output,l_temp);
882 END LOOP;
883 IF p_gen_file = 'TEXT' THEN
884 utl_file.new_line(p_l_fp);
885 END IF;
886 fnd_file.new_line(fnd_file.output);
887 END;
888
889
890
891 PROCEDURE eeoc_status_main( errbuf OUT NOCOPY VARCHAR2
892 ,retcode OUT NOCOPY NUMBER
893 ,p_report_name IN VARCHAR2
894 ,p_report_date IN VARCHAR2
895 ,p_agency_code IN VARCHAR2
896 ,p_agency_sub_code IN VARCHAR2
897 ,p_business_group IN NUMBER
898 ,p_gen_file IN VARCHAR2 DEFAULT 'TEXT'
899 --Bug # 12867515 modified the order of parameter
900 ,p_sub_agency_subelement IN VARCHAR2
901 ) IS
902 l_ret_code NUMBER;
903 l_business_group NUMBER;
904 l_log_text VARCHAR2(2000);
905 l_report_date DATE;
906 l_report_name VARCHAR2(80);
907 BEGIN
908
909
910 ghr_mto_int.set_log_program_name('GHR_EEOC_STATUS_RPT');
911 l_report_date := fnd_date.canonical_to_date(p_report_date);
912
913 l_ret_code := 0;
914 INSERT INTO fnd_sessions
915 (session_id
916 ,effective_date)
917 VALUES
918 (userenv('sessionid')
919 ,l_report_date);
920
921 IF p_business_group is NULL then
922 l_business_group := hr_general.get_business_group_id;
923 ELSE
924 l_business_group := p_business_group;
925 END IF;
926
927 populate_temp(l_business_group,l_report_date,p_agency_code,p_agency_sub_code);
928
929 -- Generate ASCII and XML files
930 l_report_name := p_report_name;
931 -- Assigning the File name.
932 IF l_report_name is NULL THEN
933 l_report_name := 'FIRES_STATUS_FY'||TO_CHAR(l_report_date,'RRRR')||p_sub_agency_subelement;
934 END IF;
935
936 WritetoFile(l_report_name,p_gen_file,l_report_date,p_sub_agency_subelement);
937
938 -- Purge the table contents after reporting
939 cleanup_table(p_type => 'EEOCSTATUS');
940
941 DELETE FROM fnd_sessions
942 WHERE session_id = userenv('sessionid');
943
944 EXCEPTION
945 WHEN OTHERS THEN
946 g_message_name := 'Unhandled Error';
947 l_log_text := 'Unhandled Error under procedure eeoc_status_main Date '||p_report_date||
948 ' ** Error Message ** : ' ||substr(sqlerrm,1,1000);
949 ghr_mto_int.log_message(p_procedure => g_message_name,
950 p_message => l_log_text);
951 COMMIT;
952
953 END;
954
955 END GHR_EEOC_STATUS_REPORT;