[Home] [Help]
PACKAGE BODY: APPS.GHR_EXEMP_PURGE
Source
1 PACKAGE BODY GHR_EXEMP_PURGE AS
2 /* $Header: ghrexpur.pkb 120.3.12020000.1 2012/07/17 05:03:46 vmididho noship $ */
3
4 g_package varchar2(40) := ' ghr_exemp_purge.';
5 g_proc varchar2(72);
6
7 PROCEDURE Main(errbuf out nocopy varchar2,
8 retcode out nocopy number,
9 p_retention_period in number,
10 p_option in VARCHAR2) IS
11
12 l_cutoff_dt DATE;
13 l_business_group number;
14
15 CURSOR fetch_ex_emps
16 IS
17 SELECT full_name, employee_number, national_identifier, person_id, effective_start_date FROM PER_ALL_PEOPLE_F WHERE EFFECTIVE_START_DATE <= l_cutoff_dt
18 AND BUSINESS_GROUP_ID = l_business_group
19 AND PERSON_TYPE_ID IN (SELECT PERSON_TYPE_ID FROM PER_PERSON_TYPES
20 WHERE BUSINESS_GROUP_ID = l_business_group AND
21 SYSTEM_PERSON_TYPE in ('EX_EMP', 'EX_CWK'))
22 AND TRUNC(EFFECTIVE_END_DATE) = FND_DATE.CANONICAL_TO_DATE('4712/12/31');
23
24 CURSOR get_months_years(p_eff_date in date)
25 IS
26 SELECT trunc(months_between(sysdate,p_eff_date)/12) years,
27 trunc(months_between(sysdate,p_eff_date)) - (12 * trunc(months_between(sysdate,p_eff_date)/12)) months
28 FROM DUAL;
29
30 CURSOR c_get_dir
31 IS
32 SELECT value
33 FROM v$parameter
34 WHERE name = 'utl_file_dir';
35
36 l_org_mgr VARCHAR2(1000);
37 l_exemp_rec EXEMP_PER_DET;
38 l_no_of_yrs NUMBER;
39 l_no_of_months NUMBER;
40 l_log_proc varchar2(30);
41 l_log_text varchar2(2000);
42 l_count NUMBER;
43 l_audit_log_dir varchar2(500);
44 l_ascii_file_name varchar2(500);
45 l_output_ascii_fname varchar2(500);
46 l_error_msg varchar2(1000);
47
48 BEGIN
49
50 g_proc := g_package||'Main';
51 hr_utility.set_location('Entering:'|| g_proc, 10);
52
53 ghr_mto_int.set_log_program_name('GHR_EXEMP_PURGE');
54 IF NVL(fnd_profile.value('GHR_ALLOW_EXEMP_PURGE'),'N') = 'N' THEN
55 l_log_proc := 'User Restriction';
56 l_log_text := 'This Option of Reporting or Purging Ex- Employees, is not allowed for this User.';
57 fnd_file.put_line(fnd_file.log,l_log_text);
58 raise rep_purge_error;
59 END IF;
60
61 l_cutoff_dt := ADD_MONTHS(SYSDATE, -(p_retention_period * 12));
62
63 l_business_group := hr_general.get_business_group_id;
64 l_count := 1;
65 FOR l_ex_emps IN fetch_ex_emps
66 LOOP
67 l_error_msg := NULL;
68 hr_utility.set_location('Loop'||l_ex_emps.national_identifier,1000);
69
70 BEGIN
71 -- Commented as validation of pa request is not required, will delete
72 -- full name will be updated to pa_requests table
73 /* BEGIN
74 ChkValPAReq(p_person_id => l_ex_emps.person_id
75 ,p_full_name => l_ex_emps.full_name);
76 EXCEPTION
77 WHEN PAREQ_APPR_EXIST THEN
78 raise;
79
80 WHEN OTHERS THEN
81 l_error_msg := substr(sqlerrm,1,1000);
82 raise rep_purge_loop_err;
83 END;*/
84
85 BEGIN
86 DetachAppUser(p_person_id => l_ex_emps.person_id);
87 EXCEPTION
88 WHEN OTHERS THEN
89 l_error_msg := substr(sqlerrm,1,1000);
90 raise rep_purge_loop_err;
91 END;
92
93
94 BEGIN
95 ValCoreTables(p_person_id => l_ex_emps.person_id,
96 p_effective_date => sysdate);
97 EXCEPTION
98 WHEN OTHERS THEN
99 l_error_msg := substr(sqlerrm,1,1000);
100 raise rep_purge_loop_err;
101 END;
102
103 IF p_option = 'Report' THEN
104 BEGIN
105 l_exemp_rec.slno := l_count;
106 l_exemp_rec.empno := l_ex_emps.employee_number;
107 l_exemp_rec.ssn := l_ex_emps.national_identifier;
108 l_exemp_rec.person_name := l_ex_emps.full_name;
109 l_exemp_rec.person_id := l_ex_emps.person_id;
110 open get_months_years(p_eff_date => l_ex_emps.effective_start_date);
111 fetch get_months_years into l_no_of_yrs, l_no_of_months;
112 close get_months_years;
113 l_exemp_rec.ret_period := l_no_of_yrs||'years ,'||l_no_of_months||'months';
114 IF l_count = 1 THEN
115 l_ascii_file_name := 'rep_exemp_purge_'||fnd_profile.value('CONC_REQUEST_ID')|| '.txt';
116 FOR rec IN c_get_dir
117 LOOP
118 l_audit_log_dir := rec.value;
119 END LOOP;
120 hr_utility.set_location(g_proc|| l_audit_log_dir, 10);
121 -- Check whether more than one util file directory is found
122 IF INSTR(l_audit_log_dir,',') > 0 THEN
123 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
124 END IF;
125 -- Find out whether the OS is MS or Unix/Linux based
126 -- If it's greater than 0, it's Unix/Linux based environment
127 IF INSTR(l_audit_log_dir,'/') > 0 THEN
128 l_output_ascii_fname := l_audit_log_dir || '/' || l_ascii_file_name;
129 ELSE
130 l_output_ascii_fname := l_audit_log_dir || '\' || l_ascii_file_name;
131 END IF;
132 END IF;
133 reportExemp(l_exemp_rec,l_count,l_audit_log_dir,l_ascii_file_name );
134 EXCEPTION
135 WHEN OTHERS THEN
136 l_error_msg := substr(sqlerrm,1,1000);
137 raise rep_purge_loop_err;
138 END;
139 ELSIF p_option = 'Purge' THEN
140 savepoint exemp_purge_sp;
141 BEGIN
142 UpdGhrTables(l_ex_emps.person_id, l_ex_emps.full_name);
143 DelGhrTables(l_ex_emps.person_id);
144 DelBenTables(l_ex_emps.person_id);
145 DelCoreTables(l_ex_emps.person_id);
146 EXCEPTION
147 WHEN OTHERS THEN
148 l_error_msg := substr(sqlerrm,1,1000);
149 raise rep_purge_del_err;
150 END;
151
152 l_log_proc := 'Records successfully Deleted';
153 l_log_text := 'Sl No: '||l_count||
154 'Name : '||l_ex_emps.full_name ||
155 'SSN : '||l_ex_emps.national_identifier||
156 'Emp No :'||l_ex_emps.employee_number;
157
158 ghr_mto_int.log_message(
159 p_procedure => l_log_proc,
160 p_message => l_log_text);
161
162 END IF;
163
164 l_count := l_count + 1;
165
166 EXCEPTION
167 WHEN pareq_appr_exist THEN
168 BEGIN
169 l_log_proc := 'Records Failed in Reporting';
170 l_log_text := 'Error in Validating PA Request'||g_proc||
171 'Name : '||l_ex_emps.full_name ||
172 'SSN : '||l_ex_emps.national_identifier||
173 'Emp No :'||l_ex_emps.employee_number||
174 'This Person is attached as a Authorizer/Requestor/Approver to RPA Actions.';
175
176 ghr_mto_int.log_message(
177 p_procedure => l_log_proc,
178 p_message => l_log_text);
179 EXCEPTION
180 WHEN others THEN
181 hr_utility.set_message(8301, 'Create Error Log failed');
182 hr_utility.raise_error;
183 END;
184 WHEN rep_purge_loop_err THEN
185 hr_utility.set_location('in rep'||substr(sqlerrm,1,1000),1000);
186 l_log_proc := 'Records Failed in Reporting';
187 l_log_text := SUBSTR('Error Reporting in the procedure'||g_proc||
188 'Name : '||l_ex_emps.full_name ||
189 'SSN : '||l_ex_emps.national_identifier||
190 'Emp No :'||l_ex_emps.employee_number||
191 'Error Msg: : '||l_error_msg,1,2000);
192
193 BEGIN
194 ghr_mto_int.log_message(
195 p_procedure => l_log_proc,
196 p_message => l_log_text);
197 EXCEPTION
198 WHEN others THEN
199 hr_utility.set_message(8301, 'Create Error Log failed');
200 hr_utility.raise_error;
201 END;
202 WHEN rep_purge_del_err THEN
203 ROLLBACK TO exemp_purge_sp;
204 l_log_proc := 'Records Failed in deletion';
205 l_log_text := SUBSTR('Error in deletion of the employee'||g_proc||
206 'Name : '||l_ex_emps.full_name ||
207 'SSN : '||l_ex_emps.national_identifier||
208 'Emp No :'||l_ex_emps.employee_number||
209 'Error Msg: : '||l_error_msg,1,2000);
210
211 BEGIN
212 ghr_mto_int.log_message(
213 p_procedure => l_log_proc,
214 p_message => l_log_text);
215 EXCEPTION
216 WHEN others THEN
217 hr_utility.set_message(8301, 'Create Error Log failed');
218 hr_utility.raise_error;
219 END;
220 END;
221 END LOOP;
222
223 l_count := l_count - 1;
224 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
225 fnd_file.put_line(fnd_file.log,'Total Records : ' || l_count );
226 fnd_file.put_line(fnd_file.log,'------------------------------------------------');
227 fnd_file.put_line(fnd_file.log,'------------Path of output file----------------');
228 fnd_file.put_line(fnd_file.log,'Text file : ' || l_output_ascii_fname);
229 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
230 COMMIT;
231
232 hr_utility.set_location('Leaving:'|| g_proc, 60);
233
234 EXCEPTION
235 WHEN rep_purge_error THEN
236 BEGIN
237 ghr_mto_int.log_message(
238 p_procedure => l_log_proc,
239 p_message => l_log_text);
240 COMMIT;
241 EXCEPTION
242 WHEN others THEN
243 hr_utility.set_message(8301, 'Create Error Log failed');
244 hr_utility.raise_error;
245 END;
246
247 WHEN OTHERS THEN
248 BEGIN
249 l_log_text := SUBSTR(
250 'Error in '||g_proc||
251 'Error Msg: : '||sqlerrm,1,2000);
252 ghr_mto_int.log_message(
253 p_procedure => 'Error While Reporting/Purging',
254 p_message => l_log_text);
255 COMMIT;
256 EXCEPTION
257 WHEN others THEN
258 hr_utility.set_message(8301, 'Create Error Log failed');
259 hr_utility.raise_error;
260 END;
261
262 hr_utility.trace_off;
263 END Main;
264
265 PROCEDURE DetachAppUser(p_person_id in NUMBER) IS
266
267 CURSOR chk_appl_user
268 IS
269 SELECT 1
270 FROM FND_USER
271 WHERE employee_id = p_person_id;
272
273 BEGIN
274
275 g_proc := g_package||'DetachAppUser';
276
277 hr_utility.set_location('Entering:'|| g_proc, 10);
278
279 FOR rec_appl_user in chk_appl_user
280 LOOP
281 BEGIN
282 UPDATE FND_USER
283 SET employee_id = NULL
284 WHERE employee_id = p_person_id;
285 EXCEPTION
286 WHEN OTHERS THEN
287 raise;
288 END;
289 END LOOP;
290
291 hr_utility.set_location('Leaving:'|| g_proc, 60);
292 END DetachAppUser;
293
294
295 PROCEDURE ChkValPAReq(p_person_id in NUMBER,
296 p_full_name in VARCHAR2) IS
297
298 CURSOR val_pareq_app
299 IS
300 SELECT 1
301 FROM GHR_PA_REQUESTS par
302 WHERE (NVL(AUTHORIZED_BY_PERSON_ID,-1) = p_person_id OR NVL(REQUESTED_BY_PERSON_ID,-1) = p_person_id
303 OR NVL(APPROVING_OFFICIAL_FULL_NAME,'@') = p_full_name)
304 AND PA_NOTIFICATION_ID IS NOT NULL
305 AND NOT EXISTS(SELECT 1 FROM GHR_PA_REQUESTS
306 WHERE ALTERED_PA_REQUEST_ID = par.PA_REQUEST_ID
307 AND PA_NOTIFICATION_ID IS NOT NULL
308 AND FIRST_NOA_CODE = '001');
309
310 l_raise_err varchar2(1);
311
312 BEGIN
313
314 g_proc := g_package||'ChkValPAReq';
315
316 hr_utility.set_location('Entering:'|| g_proc, 10);
317
318 OPEN val_pareq_app;
319 FETCH val_pareq_app INTO l_raise_err;
320 CLOSE val_pareq_app;
321
322 IF l_raise_err = '1' THEN
323 raise pareq_appr_exist;
324 END IF;
325
326 hr_utility.set_location('Leaving:'|| g_proc, 60);
327
328 END ChkValPAReq;
329
330 PROCEDURE ValCoreTables(p_person_id IN number
331 ,p_effective_date IN date)
332 IS
333 l_person_types number;
334 l_delete_permitted varchar2(1);
335 BEGIN
336
337 g_proc := g_package||'strong_predel_validation';
338
339 hr_utility.set_location('Entering:'|| g_proc, 10);
340
341 HR_PERSON_INTERNAL.weak_predel_validation
342 (p_person_id => p_person_id
343 ,p_effective_date => p_effective_date);
344 hr_utility.set_location(g_proc, 20);
345 -- check if person type changes exist.
346 SELECT count(*)
347 INTO l_person_types
348 FROM PER_PERSON_TYPE_USAGES_F ptu
349 WHERE ptu.person_id = p_person_id
350 AND ptu.effective_start_date >= p_effective_date;
351
352 IF l_person_types > 0 then
353 hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
354 hr_utility.raise_error;
355 END IF;
356
357 hr_utility.set_location(g_proc, 30);
358
359 BEGIN
360
361 SELECT 'Y'
362 INTO l_delete_permitted
363 FROM sys.dual
364 WHERE not exists (
365 SELECT null
366 FROM PER_CONTACT_RELATIONSHIPS r
367 WHERE r.person_id = p_person_id
368 OR r.contact_person_id = p_person_id);
369 EXCEPTION
370 when NO_DATA_FOUND then
371 hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
372 hr_utility.raise_error;
373 END;
374
375 hr_utility.set_location(g_proc, 40);
376
377 BEGIN
378
379 SELECT 'Y'
380 INTO l_delete_permitted
381 FROM sys.dual
382 WHERE 1 >= (
383 SELECT count(*)
384 FROM per_assignments_f a
385 WHERE a.person_id = P_PERSON_ID
386 AND a.effective_start_date >= p_effective_date);
387
388 EXCEPTION
389 when NO_DATA_FOUND then
390 hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
391 hr_utility.raise_error;
392 END;
393
394 hr_utility.set_location(g_proc, 50);
395
396 BEGIN
397
398 SELECT 'Y'
399 INTO l_delete_permitted
400 FROM sys.dual
401 WHERE not exists (
402 SELECT null
403 FROM PER_ASSIGNMENTS_F a
404 WHERE a.recruiter_id = p_person_id
405 OR a.supervisor_id = p_person_id);
406
407 EXCEPTION
408 when NO_DATA_FOUND then
409 hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
410 hr_utility.raise_error;
411 END;
412
413 hr_utility.set_location(g_proc, 60);
414
415 begin
416 --
417 select 'Y'
418 into l_delete_permitted
419 from sys.dual
420 where not exists (
421 select null
422 from per_secondary_ass_statuses s
423 where exists (
424 select null
425 from per_assignments_f a
426 where a.person_id = P_PERSON_ID
427 and a.assignment_id = s.assignment_id));
428 --
429 exception
430 when NO_DATA_FOUND then
431 hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
432 hr_utility.raise_error;
433 end;
434
435 hr_utility.set_location('Leaving:'|| g_proc, 70);
436
437 END ValCoreTables;
438
439
440
441 PROCEDURE DelCoreTables(p_person_id in NUMBER) IS
442 l_org_mgr VARCHAR2(1000);
443 BEGIN
444
445 g_proc := g_package||'DelCoreTables';
446
447 hr_utility.set_location('Entering:'|| g_proc, 10);
448
449 HR_PERSON_API.DELETE_PERSON
450 (
451 p_validate => FALSE
452 ,p_effective_date => SYSDATE
453 ,p_person_id => p_person_id
454 ,p_perform_predel_validation => FALSE
455 ,p_person_org_manager_warning => l_org_mgr);
456
457 hr_utility.set_location('Leaving:'|| g_proc, 10);
458 END ;
459
460
461 PROCEDURE DelBenTables(p_person_id in NUMBER) IS
462
463 BEGIN
464
465 g_proc := g_package||'DelBenTables';
466
467 hr_utility.set_location('Entering:'|| g_proc, 10);
468
469 DELETE FROM PAY_ELEMENT_ENTRIES_F
470 WHERE ELEMENT_ENTRY_ID IN
471 (SELECT ELEMENT_ENTRY_ID FROM PAY_ELEMENT_ENTRY_VALUES_F WHERE
472 ELEMENT_ENTRY_VALUE_ID IN (SELECT ELEMENT_ENTRY_VALUE_ID FROM BEN_PRTT_RT_VAL WHERE PRTT_ENRT_RSLT_ID
473 IN (SELECT PRTT_ENRT_RSLT_ID FROM BEN_PRTT_ENRT_RSLT_F WHERE PERSON_ID =
474 P_PERSON_ID)));
475
476 DELETE FROM PAY_ELEMENT_ENTRY_VALUES_F
477 WHERE ELEMENT_ENTRY_VALUE_ID IN
478 (SELECT ELEMENT_ENTRY_VALUE_ID FROM BEN_PRTT_RT_VAL
479 WHERE PRTT_ENRT_RSLT_ID IN (SELECT PRTT_ENRT_RSLT_ID FROM BEN_PRTT_ENRT_RSLT_F WHERE PERSON_ID =
480 P_PERSON_ID));
481
482 BEN_PERSON_DELETE.DELETE_BEN_ROWS (p_person_id => p_person_id);
483
484 hr_utility.set_location('Leaving:'|| g_proc, 60);
485
486
487 END;
488
489 PROCEDURE DelGhrTables(p_person_id in NUMBER) IS
490
491 CURSOR FETCH_RPA_ACTIONS
492 IS
493 SELECT PA_REQUEST_ID FROM
494 GHR_PA_REQUESTS
495 WHERE PERSON_ID = p_person_id;
496
497 CURSOR FETCH_COMPL_PPL
498 IS
499 SELECT COMPLAINT_ID FROM
500 GHR_COMPLAINTS2
501 WHERE COMPLAINANT_PERSON_ID = p_person_id;
502
503
504 BEGIN
505
506 g_proc := g_package||'delGhrTables';
507
508 hr_utility.set_location('Entering:'|| g_proc, 10);
509
510 FOR rpa_action in FETCH_RPA_ACTIONS
511 LOOP
512
513
514 DELETE FROM GHR_PA_REQUEST_SHADOW
515 WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;
516
517 DELETE FROM GHR_PA_REQUEST_EXTRA_INFO
518 WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;
519
520 DELETE FROM GHR_PA_REQUEST_EI_SHADOW
521 WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;
522
523 DELETE FROM GHR_PA_REMARKS
524 WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;
525
526 DELETE FROM GHR_PA_ROUTING_HISTORY
527 WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;
528
529 DELETE FROM GHR_PA_HISTORY
530 WHERE (PA_REQUEST_ID = rpa_action.PA_REQUEST_ID
531 OR
532 PERSON_ID = p_person_id);
533
534 END LOOP;
535
536 /* Deleting all the person records from GHR Tables*/
537
538 DELETE FROM GHR_PA_REQUESTS
539 WHERE PERSON_ID = p_person_id;
540
541 FOR compl_ppl in FETCH_COMPL_PPL
542 LOOP
543
544 DELETE FROM GHR_COMPL_PEOPLE
545 WHERE COMPLAINT_ID = compl_ppl.COMPLAINT_ID;
546 END LOOP;
547
548 DELETE FROM GHR_COMPLAINTS2
549 WHERE COMPLAINANT_PERSON_ID = p_person_id;
550
551 DELETE FROM GHR_COMPL_PEOPLE
552 WHERE PERSON_ID = p_person_id;
553
554 DELETE FROM GHR_RIF_REGISTERS
555 WHERE PERSON_ID = p_person_id;
556
557
558
559 hr_utility.set_location('Leaving:'|| g_proc, 60);
560
561 END;
562
563 PROCEDURE UpdGhrTables(p_person_id in NUMBER, p_full_name in VARCHAR2) IS
564 BEGIN
565 UPDATE GHR_PA_REQUESTS
566 SET REQUESTED_BY_FULL_NAME = p_full_name,
567 REQUESTED_BY_PERSON_ID = NULL
568 WHERE REQUESTED_BY_PERSON_ID = p_person_id;
569
570 UPDATE GHR_PA_REQUESTS
571 SET AUTHORIZED_BY_FULL_NAME = p_full_name,
572 AUTHORIZED_BY_PERSON_ID = NULL
573 WHERE AUTHORIZED_BY_PERSON_ID = p_person_id;
574 END;
575
576
577 PROCEDURE ReportExemp(p_exemp_rec EXEMP_PER_DET,
578 p_count number,
579 p_dir varchar2,
580 p_fname varchar2) IS
581 p_ascii_fp UTL_FILE.FILE_TYPE;
582 v_tags t_tags;
583 BEGIN
584 g_proc := g_package||'ReportExemp';
585 hr_utility.set_location('Entering:'|| g_proc, 10);
586 p_ascii_fp := utl_file.fopen(p_dir,p_fname,'w',32767);
587 WriteTagValues(p_exemp_rec,v_tags);
588 IF p_count = 1 THEN
589 WriteHeaderRow(p_ascii_fp,v_tags);
590 END IF;
591 WriteAsciivalues(p_ascii_fp,v_tags);
592 hr_utility.set_location('Leaving:'|| g_proc, 60);
593 END;
594
595
596 PROCEDURE WriteTagValues(p_exemp_rec EXEMP_PER_DET,
597 p_tags OUT NOCOPY t_tags)
598 IS
599 t_count NUMBER;
600 BEGIN
601 g_proc := g_package||'WriteTagValues';
602
603 hr_utility.set_location('Entering:'|| g_proc, 10);
604 t_count := 1;
605 -- Writing to Tags
606 p_tags(t_count).tagname := 'SLNO';
607 p_tags(t_count).tagvalue := p_exemp_rec.slno;
608 t_count := t_count+1;
609
610 p_tags(t_count).tagname := 'EMPLOYEE_NUMBER';
611 p_tags(t_count).tagvalue := p_exemp_rec.empno;
612 t_count := t_count+1;
613
614 -- Check this
615 p_tags(t_count).tagname := 'SOCIAL_SECURITY_NUMBER';
616 p_tags(t_count).tagvalue := p_exemp_rec.ssn;
617 t_count := t_count+1;
618
619 p_tags(t_count).tagname := 'PERSON_ID';
620 p_tags(t_count).tagvalue := p_exemp_rec.person_id;
621 t_count := t_count+1;
622
623 p_tags(t_count).tagname := 'PERSON_NAME';
624 p_tags(t_count).tagvalue := p_exemp_rec.person_name;
625 t_count := t_count+1;
626
627 p_tags(t_count).tagname := 'RETENTION_PERIOD';
628 p_tags(t_count).tagvalue := p_exemp_rec.ret_period;
629 t_count := t_count+1;
630
631 hr_utility.set_location('Leaving:'|| g_proc, 60);
632
633 END WriteTagValues;
634
635
636 PROCEDURE WriteAsciivalues(p_l_fp utl_file.file_type,
637 p_tags t_tags)
638 IS
639 l_temp VARCHAR2(4000);
640 l_tot NUMBER;
641 BEGIN
642 g_proc := g_package||'WriteAsciivalues';
643 hr_utility.set_location('Entering:'|| g_proc, 10);
644 l_tot := p_tags.COUNT;
645 IF l_tot > 0 THEN
646 FOR l_tags IN p_tags.FIRST .. p_tags.LAST LOOP
647 IF l_tags = l_tot THEN
648 l_temp := p_tags(l_tags).tagvalue;
649 utl_file.put_line(p_l_fp,l_temp);
650 fnd_file.put_line(fnd_file.output,l_temp);
651 ELSE
652 l_temp := p_tags(l_tags).tagvalue || '|';
653 utl_file.put(p_l_fp,l_temp);
654 fnd_file.put(fnd_file.output,l_temp);
655 END IF;
656 END LOOP;
657 END IF;
658
659 hr_utility.set_location('Leaving:'|| g_proc, 60);
660
661 END WriteAsciivalues;
662
663 ----------------------------------------------------------------------------
664 -- Writing the Header into FND Output file
665 -----------------------------------------------------------------------------
666 PROCEDURE WriteHeaderRow(p_l_fp UTL_FILE.FILE_TYPE,
667 p_tags T_TAGS)
668 IS
669 l_tot NUMBER;
670 l_temp VARCHAR2(1000);
671 BEGIN
672
673 g_proc := g_package||'WriteHeaderRow';
674
675 hr_utility.set_location('Entering:'|| g_proc, 10);
676
677 l_tot := p_tags.COUNT;
678 FOR l_tags IN p_tags.FIRST .. p_tags.LAST
679 LOOP
680 l_temp := p_tags(l_tags).tagname;
681 IF NOT(l_tags = l_tot) THEN
682 l_temp := l_temp||'|';
683 END IF;
684 utl_file.put(p_l_fp,l_temp);
685 fnd_file.put(fnd_file.output,l_temp);
686 END LOOP;
687 utl_file.new_line(p_l_fp);
688 fnd_file.new_line(fnd_file.output);
689
690 hr_utility.set_location('Leaving:'|| g_proc, 60);
691
692 END WriteHeaderRow;
693
694 END GHR_EXEMP_PURGE;