[Home] [Help]
PACKAGE BODY: APPS.GHR_EEOC_DYNAMICS_REPORT
Source
1 PACKAGE BODY GHR_EEOC_DYNAMICS_REPORT AS
2 /* $Header: ghreeocd.pkb 120.7 2011/08/16 12:21:22 vmididho noship $ */
3
4 ---------------------------------------------------------------------------------------------
5 -- This is the procedure to populate values into the temporary table GHR_CPDF_TEMP
6 ---------------------------------------------------------------------------------------------
7 PROCEDURE Populate_Temp (p_business_group IN VARCHAR2
8 ,p_report_st_dt IN DATE
9 ,p_report_end_dt 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_effective_start_date DATE;
16 l_value NUMBER;
17 l_full_name PER_ALL_PEOPLE.FULL_NAME%TYPE;
18 l_emp_no PER_ALL_PEOPLE.EMPLOYEE_NUMBER%TYPE;
19 l_log_text VARCHAR2(2000);
20 l_records_found BOOLEAN := FALSE;
21 l_mesgbuff1 VARCHAR2(4000);
22
23
24 cursor get_par_actions
25 is
26 SELECT par.*, per.full_name, per.employee_number
27 FROM ghr_pa_requests par,
28 per_all_people_f per
29 WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency_code||NVL(p_agency_sub_code,'%')
30 AND par.person_id = per.person_id
31 AND trunc(par.effective_date) BETWEEN p_report_st_dt AND p_report_end_dt
32 AND trunc(par.effective_date) BETWEEN per.effective_start_date AND per.effective_end_date
33 AND par.status IN ('UPDATE_HR_COMPLETE')
34 AND Exclude_Noac(par.first_noa_code) <> 'TRUE'
35 -- Bug # 12862346
36 AND NOT EXISTS (select 1
37 from ghr_pa_requests b
38 where altered_pa_request_id = par.pa_request_id
39 and pa_notification_id is not null
40 and first_noa_code = '001'
41 and second_noa_code = par.first_noa_code)
42 AND decode(hr_general.get_xbg_profile,'Y',per.business_group_id ,p_business_group) = per.business_group_id;
43
44 CURSOR get_awd_amount(p_pa_request_id in number)
45 is
46 select award_amount
47 from ghr_pa_requests
48 where first_noa_code = '002'
49 and pa_notification_id is not null
50 connect by prior pa_request_id = altered_pa_request_id
51 start with altered_pa_request_id = p_pa_request_id
52 order by pa_notification_id desc;
53
54 BEGIN
55
56 G_TEMP_REC := NULL;
57 GHR_EEOC_STATUS_REPORT.Cleanup_Table(p_type => 'EEOCDYNAMICS');
58 FOR l_par_rec IN get_par_actions
59 LOOP
60 BEGIN
61 g_temp_rec := NULL;
62 g_message_name := NULL;
63 l_records_found := TRUE;
64
65 l_full_name := l_par_rec.full_name;
66 l_emp_no := l_par_rec.employee_number;
67
68 g_temp_rec.effective_date := l_par_rec.effective_date;
69 g_temp_rec.first_noa_code := l_par_rec.first_noa_code;
70 g_temp_rec.agency_code := NVL(l_par_rec.agency_code,l_par_rec.from_agency_code);
71 -- Bug # 12865107 Reporting Employee Number instead of Employee Id
72 -- as employee_number field not available in GHR_CPDF_TEMP using from national_identifier
73 g_temp_rec.from_national_identifier := l_emp_no;
74
75
76 l_peopei_data := null;
77 GHR_HISTORY_FETCH.fetch_peopleei(
78 p_person_id => l_par_rec.person_id,
79 p_information_type => 'GHR_US_PER_GROUP1',
80 p_date_effective => g_temp_rec.effective_date,
81 p_per_ei_data => l_peopei_data
82 );
83
84 g_temp_rec.ehri_employee_id := to_number(l_PEOPEI_DATA.PEI_INFORMATION18);
85
86
87
88 IF l_par_rec.noa_family_code = 'AWARD' THEN
89 g_message_name := 'Fetch Element: Award';
90 l_value := l_par_rec.award_amount;
91 FOR rec_awd_amount in get_awd_amount(p_pa_request_id => l_par_rec.pa_request_id)
92 LOOP
93 l_value := rec_awd_amount.award_amount;
94 EXIT;
95 END LOOP;
96
97 IF NVL(l_par_rec.award_uom,'M')='M' THEN
98 g_temp_rec.award_dollars := l_value;
99 END IF;
100
101 IF NVL(l_par_rec.award_uom,'M')='H' THEN
102 g_temp_rec.award_hours := l_value;
103 END IF;
104 END IF;
105
106 insert_row;
107
108 hr_utility.set_location('After Insert Row',1000);
109
110 EXCEPTION
111 WHEN EEOC_DYNAMICS_RPT_ERR THEN
112 hr_utility.set_location('Inside EEOC_DYNAMICS_RPT_ERR exception ',30);
113 ghr_mto_int.log_message(p_procedure => g_message_name,
114 p_message => l_log_text);
115 COMMIT;
116 END;
117 END LOOP;
118
119 IF NOT l_records_found THEN
120 g_message_name:='RECORDS_NOT_FOUND';
121 l_log_text:= 'No Records found for the period '||p_report_st_dt||' - '||p_report_end_dt;
122 ghr_mto_int.log_message(p_procedure => g_message_name,
123 p_message => l_log_text
124 );
125
126 l_mesgbuff1:='No Records found for the period '||p_report_st_dt||' - '||p_report_end_dt;
127 fnd_file.put(fnd_file.log,l_mesgbuff1);
128 fnd_file.new_line(fnd_file.log);
129 END IF;
130
131 END Populate_temp;
132
133 FUNCTION Exclude_Noac (p_noa_code IN VARCHAR2) RETURN VARCHAR2 IS
134
135 BEGIN
136 -- Bug # 12862366 Modified Noas as per the requirement
137 IF (p_noa_code like '1%') OR (p_noa_code like '5%') OR (p_noa_code like '3%') OR (p_noa_code in ('840','841','842','843','844','845','846','847','849','878','879',
138 '885','886','887','892','889')) THEN
139
140 RETURN('FALSE');
141 ELSE
142 RETURN('TRUE');
143 END IF;
144
145 END Exclude_Noac;
146
147 PROCEDURE Insert_Row IS
148 l_proc varchar2(30) := 'insert_row';
149 l_log_text ghr_process_log.log_text%type;
150 l_message_name ghr_process_log.message_name%type;
151
152 BEGIN
153 hr_utility.set_location('Entering:'||l_proc,5);
154
155 INSERT INTO ghr_cpdf_temp(report_type
156 ,session_id
157 ,agency_code
158 ,effective_date
159 ,from_national_identifier
160 ,ehri_employee_id
161 ,first_noa_code
162 ,award_dollars
163 ,award_hours)
164 values (
165 'EEOCDYNAMICS'
166 ,userenv('SESSIONID')
167 ,g_temp_rec.agency_code
168 ,g_temp_rec.effective_date
169 ,g_temp_rec.from_national_identifier
170 ,g_temp_rec.ehri_employee_id
171 ,g_temp_rec.first_noa_code
172 ,g_temp_rec.award_dollars
173 ,g_temp_rec.award_hours);
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 g_message_name := 'Unhandled Error';
178 l_log_text := 'Unhandled Error under procedure insert_row'||
179 '; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
180 ghr_mto_int.log_message(p_procedure => g_message_name,
181 p_message => l_log_text);
182 COMMIT;
183
184 END Insert_Row;
185
186 ---------------------------------------------------------------------------
187 --- THIS IS PROC TO GENERATE THE ASCII and XML file
188 ---------------------------------------------------------------------------
189 PROCEDURE WritetoFile(p_input_file_name IN VARCHAR2
190 ,p_gen_file IN VARCHAR2
191 ,p_report_end_dt IN DATE
192 ,p_sub_agency_subelement IN VARCHAR2)
193 IS
194 p_xml_fp UTL_FILE.FILE_TYPE;
195 p_ascii_fp UTL_FILE.FILE_TYPE;
196 l_audit_log_dir varchar2(500);
197 l_xml_file_name varchar2(500);
198 l_ascii_file_name varchar2(500);
199 l_output_xml_fname varchar2(500);
200 l_output_ascii_fname varchar2(500);
201 v_tags t_tags;
202 l_count NUMBER;
203 l_session_id NUMBER;
204 l_request_id NUMBER;
205 l_temp VARCHAR2(500);
206
207 CURSOR c_eeoc_dynamics(c_session_id NUMBER)
208 IS
209 SELECT *
210 FROM GHR_CPDF_TEMP
211 WHERE SESSION_ID = c_session_id
212 AND REPORT_TYPE = 'EEOCDYNAMICS'
213 ORDER BY AGENCY_CODE,TO_NATIONAL_IDENTIFIER,EFFECTIVE_DATE;
214
215 CURSOR c_get_dir
216 IS
217 SELECT value
218 FROM V$PARAMETER
219 WHERE NAME = 'utl_file_dir';
220
221 BEGIN
222 -- Assigning the File name.
223 l_xml_file_name := p_input_file_name || '.xml';
224 l_ascii_file_name := p_input_file_name || '.txt';
225 l_count := 1;
226 l_session_id := USERENV('SESSIONID');
227
228 FOR rec IN c_get_dir
229 LOOP
230 l_audit_log_dir := rec.value;
231 END LOOP;
232
233 -- Check whether more than one util file directory is found
234 IF INSTR(l_audit_log_dir,',') > 0 THEN
235 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
236 END IF;
237
238
239 -- Find out whether the OS is MS or Unix/Linux based
240 -- If it's greater than 0, it's Unix/Linux based environment
241 IF INSTR(l_audit_log_dir,'/') > 0 THEN
242 l_output_xml_fname := l_audit_log_dir || '/' || l_xml_file_name;
243 l_output_ascii_fname := l_audit_log_dir || '/' || l_ascii_file_name;
244 ELSE
245 l_output_xml_fname := l_audit_log_dir || '\' || l_xml_file_name;
246 l_output_ascii_fname := l_audit_log_dir || '\' || l_ascii_file_name;
247 END IF;
248
249 IF p_gen_file = 'XML' THEN
250 p_xml_fp := utl_file.fopen(l_audit_log_dir,l_xml_file_name,'w',32767);
251 utl_file.put_line(p_xml_fp,'<?xml version="1.0" encoding="UTF-8"?>');
252 -- Writing from and to dates
253 utl_file.put_line(p_xml_fp,'<Records>');
254 -- Loop through cursor and write the values into the XML and ASCII File.
255 FOR ctr_table IN c_eeoc_dynamics(l_session_id) LOOP
256 WriteTagValues(ctr_table,v_tags,p_report_end_dt,p_sub_agency_subelement);
257 utl_file.put_line(p_xml_fp,'<Record' || l_count || '>');
258 WriteXMLvalues(p_xml_fp,v_tags);
259 utl_file.put_line(p_xml_fp,'</Record' || l_count || '>');
260 IF l_count = 1 THEN
261 WriteHeaderRow(p_ascii_fp,v_tags,p_gen_file);
262 END IF;
263 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_file);
264 l_count := l_count + 1;
265 END LOOP;
266 utl_file.put_line(p_xml_fp,'</Records>');
267 utl_file.fclose(p_xml_fp);
268 ELSE
269 p_ascii_fp := utl_file.fopen(l_audit_log_dir,l_ascii_file_name,'w',32767);
270 FOR ctr_table IN c_eeoc_dynamics(l_session_id) LOOP
271 WriteTagValues(ctr_table,v_tags,p_report_end_dt,p_sub_agency_subelement);
272 IF l_count = 1 THEN
273 WriteHeaderRow(p_ascii_fp,v_tags,p_gen_file);
274 END IF;
275 WriteAsciivalues(p_ascii_fp,v_tags,p_gen_file);
276 l_count := l_count + 1;
277 END LOOP;
278 END IF;
279
280 l_count := l_count - 1;
281 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
282 fnd_file.put_line(fnd_file.log,'Total Records : ' || l_count );
283 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
284 -- Write the end tag and close the XML File.
285
286 fnd_file.put_line(fnd_file.log,'------------Path of output file----------------');
287 IF p_gen_file = 'XML' THEN
288 fnd_file.put_line(fnd_file.log,'XML file : ' || l_output_xml_fname);
289 END IF;
290 IF p_gen_file = 'TEXT' THEN
291 fnd_file.put_line(fnd_file.log,'Text file : ' || l_output_ascii_fname);
292 END IF;
293 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
294
295 END WritetoFile;
296
297 PROCEDURE WriteTagValues(p_eeoc_rec GHR_CPDF_TEMP%ROWTYPE,
298 p_tags OUT NOCOPY t_tags,
299 p_report_end_dt IN DATE,
300 p_sub_agency_subelement IN VARCHAR2)
301 IS
302 l_count NUMBER;
303
304 BEGIN
305 l_count := 1;
306 -- Writing to Tags
307 p_tags(l_count).tagname := 'DATE_SUBMITTED';
308 p_tags(l_count).tagvalue := TO_CHAR(trunc(sysdate), 'YYYYMMDD');
309 l_count := l_count+1;
310
311 p_tags(l_count).tagname := 'REPORT_FY';
312 p_tags(l_count).tagvalue := TO_CHAR(p_report_end_dt,'YYYY');
313 l_count := l_count+1;
314
315 -- Check this
316 p_tags(l_count).tagname := 'AGENCY_FIRES';
317 p_tags(l_count).tagvalue := p_sub_agency_subelement;
318 l_count := l_count+1;
319
320 --Bug # 12865107 Added Employee Number
321 p_tags(l_count).tagname := 'EMPLOYEE_UNIQUE_ID';
322 IF p_eeoc_rec.ehri_employee_id is NOT NULL then
323 p_tags(l_count).tagvalue := p_eeoc_rec.ehri_employee_id ;
324 ELSE
325 p_tags(l_count).tagvalue := p_eeoc_rec.from_national_identifier ;
326 END IF;
327 l_count := l_count+1;
328
329 p_tags(l_count).tagname := 'EMP_AGENCY_CPDF';
330 p_tags(l_count).tagvalue := p_eeoc_rec.agency_code;
331 l_count := l_count+1;
332
333 --Bug# 12860826
334 p_tags(l_count).tagname := 'EFFECTIVE_DATE';
335 p_tags(l_count).tagvalue := TO_CHAR(trunc(p_eeoc_rec.effective_date),'YYYYMMDD');
336 l_count := l_count+1;
337
338 p_tags(l_count).tagname := 'NATURE_OF_ACTION';
339 p_tags(l_count).tagvalue := p_eeoc_rec.first_noa_code;
340 l_count := l_count+1;
341
342 p_tags(l_count).tagname := 'AWARD_AMOUNT';
343 p_tags(l_count).tagvalue := replace(NVL(p_eeoc_rec.award_dollars,0),'.','');
344 l_count := l_count+1;
345
346 p_tags(l_count).tagname := 'AWARD_HOURS';
347 p_tags(l_count).tagvalue := NVL(p_eeoc_rec.award_hours,0);
348 l_count := l_count+1;
349
350 p_tags(l_count).tagname := 'QUALITY_STEP_INCREASE';
351 IF p_eeoc_rec.first_noa_code = '892' THEN
352 p_tags(l_count).tagvalue := 'Y';
353 ELSE
354 p_tags(l_count).tagvalue := 'N';
355 END IF;
356
357 l_count := l_count+1;
358
359
360
361 END WriteTagValues;
362
363 -----------------------------------------------------------------------------
364 -- Writing the records from PL/SQL table p_tags into XML File
365 -----------------------------------------------------------------------------
366 PROCEDURE WriteXMLvalues(p_l_fp utl_file.file_type,
367 p_tags t_tags)
368 IS
369 BEGIN
370 FOR l_tags IN p_tags.FIRST .. p_tags.LAST
371 LOOP
372 utl_file.put_line(p_l_fp,'<' || p_tags(l_tags).tagname || '>' || p_tags(l_tags).tagvalue || '</' || p_tags(l_tags).tagname || '>');
373 END LOOP;
374 END WriteXMLvalues;
375
376 -----------------------------------------------------------------------------
377 -- Writing the records from PL/SQL table p_tags into Text and FND Output File
378 -----------------------------------------------------------------------------
379 PROCEDURE WriteAsciivalues(p_l_fp utl_file.file_type,
380 p_tags t_tags,
381 p_gen_file IN VARCHAR2 )
382 IS
383 l_temp VARCHAR2(4000);
384 l_tot NUMBER;
385 BEGIN
386 l_tot := p_tags.COUNT;
387 IF l_tot > 0 THEN
388 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
389 IF l_tags = l_tot THEN
390 l_temp := p_tags(l_tags).tagvalue;
391 IF p_gen_file = 'TEXT' THEN
392 utl_file.put_line(p_l_fp,l_temp);
393 END IF;
394 fnd_file.put_line(fnd_file.output,l_temp);
395 ELSE
396 l_temp := p_tags(l_tags).tagvalue || '|';
397 IF p_gen_file = 'TEXT' THEN
398 utl_file.put(p_l_fp,l_temp);
399 END IF;
400 fnd_file.put(fnd_file.output,l_temp);
401 END IF;
402 END LOOP;
403 END IF;
404
405 END WriteAsciivalues;
406
407 ----------------------------------------------------------------------------
408 -- Writing the Header into FND Output file
409 -----------------------------------------------------------------------------
410 PROCEDURE WriteHeaderRow(p_l_fp UTL_FILE.FILE_TYPE,
411 p_tags T_TAGS,
412 p_gen_file IN VARCHAR2)
413 IS
414 l_tot NUMBER;
415 l_temp VARCHAR2(1000);
416 BEGIN
417 l_tot := p_tags.COUNT;
418 FOR l_tags IN p_tags.FIRST .. p_tags.LAST
419 LOOP
420 l_temp := p_tags(l_tags).tagname;
421 IF NOT(l_tags = l_tot) THEN
422 l_temp := l_temp||'|';
423 END IF;
424 IF p_gen_file = 'TEXT' THEN
425 utl_file.put(p_l_fp,l_temp);
426 END IF;
427
428 fnd_file.put(fnd_file.output,l_temp);
429 END LOOP;
430 IF p_gen_file = 'TEXT' THEN
431 utl_file.new_line(p_l_fp);
432 END IF;
433 fnd_file.new_line(fnd_file.output);
434 END;
435
436
437 PROCEDURE Eeoc_Dynamics_Main( errbuf OUT NOCOPY VARCHAR2
438 ,retcode OUT NOCOPY NUMBER
439 ,p_report_name IN VARCHAR2
440 ,p_report_st_dt IN VARCHAR2
441 ,p_report_end_dt IN VARCHAR2
442 ,p_agency_code IN VARCHAR2
443 ,p_agency_sub_code IN VARCHAR2
444 ,p_business_group IN NUMBER
445 ,p_gen_file IN VARCHAR2 DEFAULT 'TEXT'
446 --Bug # 12867515 modified the order of parameter
447 ,p_sub_agency_subelement IN VARCHAR2
448 ) IS
449 l_ret_code NUMBER;
450 l_business_group NUMBER;
451 l_log_text VARCHAR2(2000);
452 l_report_st_dt DATE;
453 l_report_end_dt DATE;
454 l_report_name VARCHAR2(80);
455 BEGIN
456 ghr_mto_int.set_log_program_name('GHR_EEOC_DYN_RPT');
457
458 l_report_st_dt := fnd_date.canonical_to_date(p_report_st_dt);
459 l_report_end_dt := fnd_date.canonical_to_date(p_report_end_dt);
460
461 l_ret_code := 0;
462
463 IF p_business_group is NULL then
464 l_business_group := hr_general.get_business_group_id;
465 ELSE
466 l_business_group := p_business_group;
467 END IF;
468
469
470 Populate_Temp(l_business_group,l_report_st_dt,l_report_end_dt,p_agency_code,p_agency_sub_code);
471
472 -- Generate ASCII and XML files
473 l_report_name := p_report_name;
474 -- Assigning the File name.
475 IF l_report_name is NULL THEN
476 l_report_name := 'FIRES_DYNAMICS_FY'||TO_CHAR(l_report_end_dt,'RRRR')||p_sub_agency_subelement;
477 END IF;
478
479
480 WriteToFile(l_report_name,p_gen_file,l_report_end_dt,p_sub_agency_subelement);
481
482 -- Purge the table contents after reporting
483 GHR_EEOC_STATUS_REPORT.Cleanup_Table(p_type => 'EEOCDYNAMICS');
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 g_message_name := 'Unhandled Error';
488 l_log_text := 'Unhandled Error under procedure eeoc_dynamics_main Date '
489 ||' ** Error Message ** : ' ||substr(sqlerrm,1,1000);
490 ghr_mto_int.log_message(p_procedure => g_message_name,
491 p_message => l_log_text);
492 COMMIT;
493
494 END Eeoc_Dynamics_Main;
495
496 END GHR_EEOC_DYNAMICS_REPORT;