1 PACKAGE BODY PER_PERUSEOX_XMLP_PKG AS
2 /* $Header: PERUSEOXB.pls 120.4 2011/05/12 07:15:50 nvelaga ship $ */
3
4 function BeforeReport return boolean is
5
6 l_dummy varchar2(1);
7 l_buffer varchar2(1000);
8 g_delimiter varchar2(1) := ',';
9 g_eol varchar2(1) := fnd_global.local_chr(10);
10 x boolean;
11 begin
12 P_PAYROLL_PERIOD_DATE_START_1:=TO_DATE(substr(P_PAYROLL_PERIOD_DATE_START,1,10),'YYYY/MM/DD');
13 P_PAYROLL_PERIOD_DATE_END_1:=TO_DATE(substr(P_PAYROLL_PERIOD_DATE_END,1,10),'YYYY/MM/DD');
14
15 -- hr_standard.event('BEFORE REPORT');
16 x := P_REPORT_YEARValidTrigger ;
17 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
18 c_report_date := trunc(sysdate);
19 if P_PAYROLL_PERIOD_DATE_START is not null
20 then
21 c_report_year:= to_char(fnd_date.canonical_to_date(P_PAYROLL_PERIOD_DATE_START),'RRRR');
22 else
23 c_report_year:= to_char(sysdate,'RRRR');
24 end if;
25 select
26 pgh.name, pgv.version_number, pgn.entity_id, pgn.hierarchy_node_id
27 into
28 c_hierarchy_name, c_hierarchy_version_num, c_parent_org_id, c_parent_node_id
29 from
30 per_gen_hierarchy pgh,
31 per_gen_hierarchy_versions pgv,
32 per_gen_hierarchy_nodes pgn
33 where
34 pgh.hierarchy_id = p_hierarchy_id
35 and pgh.hierarchy_id = pgv.hierarchy_id
36 and pgv.hierarchy_version_id = p_hierarchy_version_id
37 and pgn.hierarchy_version_id = pgv.hierarchy_version_id
38 and pgn.node_type = 'PAR';
39 /*srw.message('05','c_parent_node_id : '||c_parent_node_id);*/null;
40
41 /*srw.message('05','c_parent_org_id : '||c_parent_org_id);*/null;
42
43 /*srw.message('05','c_hierarchy_vsn_num: '||c_hierarchy_version_num);*/null;
44
45 /*srw.message('05','c_hierarchy_name : '||c_hierarchy_name);*/null;
46
47
48 begin
49 select null
50 into l_dummy
51 from hr_all_organization_units
52 where organization_id = c_parent_org_id
53 and location_id is not null;
54 exception
55 when no_data_found then
56 fnd_message.set_name('PER','PER_75228_ORG_LOC_MISSING');
57 /*srw.message('999',fnd_message.get);*/null;
58
59 raise;
60 end;
61 begin
62 select null
63 into l_dummy
64 from hr_organization_information
65 where organization_id = c_parent_org_id
66 and org_information_context = 'EEO_Spec';
67 exception
68 when no_data_found then
69 fnd_message.set_name('PER','PER_75229_EEO_CLASS_MISSING');
70 /*srw.message('999',fnd_message.get);*/null;
71
72 raise;
73 end;
74 begin
75 select null
76 into l_dummy
77 from hr_location_extra_info hlei1,
78 hr_location_extra_info hlei2,
79 per_gen_hierarchy_nodes pgn,
80 hr_locations_all eloc
81 where pgn.hierarchy_version_id = p_hierarchy_version_id
82 and pgn.node_type = 'EST'
83 and eloc.location_id = pgn.entity_id
84 and hlei1.location_id = eloc.location_id
85 and hlei1.information_type = 'EEO-1 Specific Information'
86 and hlei1.lei_information_category= 'EEO-1 Specific Information'
87 and hlei2.location_id = eloc.location_id
88 and hlei2.information_type = 'Establishment Information'
89 and hlei2.lei_information_category= 'Establishment Information';
90 exception
91 when no_data_found then
92 fnd_message.set_name('PER','PER_75230_EST_CLASS_MISSING');
93 /*srw.message('999',fnd_message.get);*/null;
94
95 raise;
96 when others then
97 null;
98 end;
99 if P_AUDIT_REPORT = 'Y' then
100 /* file_io.open;
101 l_buffer := 'Person Id' || g_delimiter ||
102 'Employee Last Name' || g_delimiter ||
103 'Employee First Name' || g_delimiter ||
104 'Employee Number' || g_delimiter ||
105 'Gender' || g_delimiter ||
106 'Ethnic Origin' || g_delimiter ||
107 'Employee Category' || g_delimiter ||
108 'Assignment Type' || g_delimiter ||
109 'Location Id' || g_delimiter ||
110 'Location' || g_delimiter ||
111 'Reason' ||
112 g_eol;
113 file_io.put(l_buffer); */
114 null;
115 end if;
116 RETURN True;
117 end;
118
119 function P_REPORT_YEARValidTrigger return boolean is
120 begin
121 if P_PAYROLL_PERIOD_DATE_START is not null
122 then
123 p_report_year:= to_char(fnd_date.canonical_to_date(P_PAYROLL_PERIOD_DATE_START),'RRRR');
124 else
125 p_report_year:= to_char(sysdate,'RRRR');
126 end if;
127 return (TRUE);
128 end;
129
130 function AfterReport return boolean is
131 begin
132
133 -- hr_standard.event('AFTER REPORT');
134 if P_AUDIT_REPORT = 'Y' then
135 null;
136 -- file_io.close;
137 end if;
138
139 return (TRUE);
140 end;
141
142 function cf_set_detailsformula(person_id1 in number, report_date_end in date, ASS_LOC in number, location_id in number, address1 in varchar2) return number is
143 l_ex_reason varchar2(500) := null;
144 l_counted char(1) := 'N';
145 l_exists char(1) := 'N';
146 l_name varchar2(150) := null;
147 l_emp_num varchar2(50) := null;
148 l_sex varchar2(10) := null;
149 l_buffer varchar2(2000);
150 g_delimiter varchar2(1) := ',';
151 g_eol varchar2(1) := fnd_global.local_chr(10);
152
153 -- Added for bug#11736960
154 l_ethnic_origin number(2) := null;
155 l_two_or_more varchar2(1) := 'N';
156
157 cursor c_location is
158 select 'Y'
159 from
160 per_all_assignments_f ass
161 where
162 ass.person_id = person_id1
163 and report_date_end between ass.effective_start_date
164 and ass.effective_end_date
165 and ass.assignment_type = 'E'
166 and ass.primary_flag = 'Y'
167 and (ass.location_id is null
168 or ass.location_id not in
169 (select entity_id
170 from per_gen_hierarchy_nodes
171 where hierarchy_version_id = p_hierarchy_version_id
172 and node_type <> 'PAR'))
173 ;
174
175 cursor c_assignment is
176 select 'Y'
177 from
178 per_all_assignments_f ass
179 where
180 ass.person_id = person_id1
181 and ass.business_group_id = P_BUSINESS_GROUP_ID
182 and report_date_end between ass.effective_start_date and ass.effective_end_date
183
184 and ass.assignment_type = 'E'
185 and ass.primary_flag = 'Y'
186 and exists (select 'x'
187 from hr_organization_information hoi1
188 where to_char(ass.assignment_status_type_id) = hoi1.org_information1
189 and hoi1.org_information_context = 'Reporting Statuses'
190 and hoi1.organization_id = P_BUSINESS_GROUP_ID);
191
192 /* Commented for bug#11736960 starts
193 cursor c_ethnic is
194 select 'Y'
195 from per_all_people_f peo
196 where
197 (
198 (peo.per_information1 is not null
199 and exists
200 (select null
201 from hr_lookups
202 where peo.per_information1 = lookup_code
203 and lookup_type = 'US_ETHNIC_GROUP'
204 )
205 )
206 )
207 and peo.person_id = person_id1
208 and peo.per_information_category = 'US'
209 and peo.business_group_id = P_BUSINESS_GROUP_ID
210 and report_date_end between peo.effective_start_date
211 and peo.effective_end_date
212 ;
213 Commented for bug#11736960 ends */
214
215 cursor c_emp_category is
216 select 'Y'
217 from
218 per_all_assignments_f ass
219 where
220 ass.person_id = person_id1
221 and ass.business_group_id = P_BUSINESS_GROUP_ID
222 and report_date_end between ass.effective_start_date
223 and ass.effective_end_date
224
225 and ass.assignment_type = 'E'
226 and ass.primary_flag = 'Y'
227 and ass.employment_category is not null
228 and exists (select 'x'
229 from hr_organization_information hoi2
230 where ass.employment_category = hoi2.org_information1
231 and hoi2.org_information_context = 'Reporting Categories'
232 and hoi2.organization_id = P_BUSINESS_GROUP_ID);
233
234 cursor c_job_category is
235 select 'Y'
236 from
237 per_all_assignments_f ass
238 ,per_jobs job
239 where
240 ass.person_id = person_id1
241 and ass.business_group_id = P_BUSINESS_GROUP_ID
242 and job.business_group_id = P_BUSINESS_GROUP_ID
243 and report_date_end between ass.effective_start_date
244 and ass.effective_end_date
245
246 and ass.assignment_type = 'E'
247 and ass.primary_flag = 'Y'
248 and ass.job_id is not null
249 and ass.job_id = job.job_id
250 and job.job_information_category = 'US'
251 and report_date_end between job.date_from
252 and nvl(job.date_to, report_date_end)
253 and job.job_information1 in
254 (select lookup_code
255 from hr_lookups
256 where lookup_type = 'US_EEO1_JOB_CATEGORIES')
257 ;
258
259 cursor c_person_name is
260 select substr(peo.full_name,1,150) emp_name,
261 peo.employee_number emp_num,
262 peo.sex sex
263 from per_all_people_f peo
264 where peo.person_id = person_id1
265 and peo.business_group_id = P_BUSINESS_GROUP_ID
266 and report_date_end between peo.effective_start_date
267 and peo.effective_end_date
268 ;
269
270 cursor c_job_cat is
271 select nvl(jbt.name, 'Not Specified')||' '||nvl(lup.meaning,'') job_cat
272 from hr_lookups lup
273 ,per_all_assignments_f ass
274 ,per_jobs_vl job
275 ,per_jobs_tl jbt
276 where ass.person_id = person_id1
277
278 and report_date_end between ass.effective_start_date
279 and ass.effective_end_date
280 and report_date_end between job.date_from
281 and nvl(job.date_to, report_date_end)
282 and ass.assignment_type = 'E'
283 and ass.primary_flag = 'Y'
284 and ass.business_group_id = P_BUSINESS_GROUP_ID
285 and job.business_group_id = P_BUSINESS_GROUP_ID
286 and ass.job_id = jbt.job_id (+)
287 and jbt.language(+) = userenv('LANG')
288 and ass.job_id = job.job_id (+)
289 and job.job_information1 = lup.lookup_code(+)
290 and lup.lookup_type(+) = 'US_EEO1_JOB_CATEGORIES'
291 and job.job_information_category(+) = 'US' ;
292
293 /* Commented for bug#11736960 starts
294 cursor c_eth_cat is
295 select nvl(lup.meaning, 'Not Specified') ethnic
296 from hr_lookups lup
297 ,per_all_people_f peo
298 where peo.person_id = person_id1
299 and peo.business_group_id = P_BUSINESS_GROUP_ID
300 and peo.per_information_category(+) = 'US'
301 and peo.per_information1 = lup.lookup_code(+)
302 and lup.lookup_type(+) = 'US_ETHNIC_GROUP'
303 and report_date_end between peo.effective_start_date
304 and peo.effective_end_date
305
306 ;
307 Commented for bug#11736960 ends */
308
309 cursor c_emp_cat is
310 select nvl(lup.meaning, 'Not Specified') emp_cat
311 from hr_lookups lup
312 ,per_all_assignments_f ass
313 where ass.person_id = person_id1
314 and ass.business_group_id = P_BUSINESS_GROUP_ID
315 and ass.employment_category = lup.lookup_code(+)
316 and lup.lookup_type(+) = 'EMP_CAT'
317
318 and report_date_end between ass.effective_start_date
319 and ass.effective_end_date
320 and ass.assignment_type = 'E'
321 and ass.primary_flag = 'Y';
322
323 cursor c_ass_type is
324 select past.user_status ustat
325 from per_all_assignments_f ass
326 ,per_assignment_status_types past
327 where ass.person_id = person_id1
328 and ass.business_group_id = P_BUSINESS_GROUP_ID
329
330 and report_date_end between ass.effective_start_date
331 and ass.effective_end_date
332 and ass.assignment_type = 'E'
333 and ass.primary_flag = 'Y'
334 and ass.assignment_status_type_id = past.assignment_status_type_id
335 and past.active_flag = 'Y'
336 and past.primary_flag = 'P'
337 ;
338
339 cursor c_eeo1_extra_info is
340 select 'Y'
341 from
342 per_all_assignments_f ass
343 ,hr_location_extra_info hlei1
344 ,hr_location_extra_info hlei2
345 where
346 ass.person_id = person_id1
347 and report_date_end between ass.effective_start_date
348 and ass.effective_end_date
349 and ass.assignment_type = 'E'
350 and ass.primary_flag = 'Y'
351 and to_char(hlei1.location_id) = ass.location_id
352 and to_char(hlei2.location_id) = ass.location_id
353 and hlei1.location_id = hlei2.location_id
354 and hlei1.information_type = 'EEO-1 Specific Information'
355 and hlei1.lei_information_category= 'EEO-1 Specific Information'
356 and hlei2.information_type = 'Establishment Information'
357 and hlei2.lei_information_category= 'Establishment Information';
358
359 begin
360
361
362 if ASS_LOC <> c_ass_loc then
363 cp_display := 0;
364 end if;
365
366 c_ass_loc := ASS_LOC;
367
368 open c_location;
369 fetch c_location into l_exists;
370 if c_location%found then
371 l_counted := 'Y';
372 l_ex_reason := 'Loc not in hierarchy';
373 end if;
374 close c_location;
375
376 -- Added for bug#11736960
377 l_ethnic_origin := per_us_hr_utility_pkg.derive_alien_ethnic_origin(person_id1);
378
379 if l_ethnic_origin = 99 then
380 l_counted := 'Y';
381 if l_ex_reason is not null then
382 l_ex_reason := l_ex_reason ||
383 ', Ethnic Origin(EIT) information is missing or All race fields are set to ''No''';
384 else
385 l_ex_reason := 'Ethnic Origin(EIT) information is missing or All race fields are set to ''No''';
386 end if;
387 end if;
388 --
389 /* Commented for bug#11736960 starts
390 open c_ethnic;
391 fetch c_ethnic into l_exists;
392 if c_ethnic%notfound then
393 l_counted := 'Y';
394 if l_ex_reason is not null then
398 end if;
395 l_ex_reason := l_ex_reason||', No Ethnic Origin';
396 else
397 l_ex_reason := 'No Ethnic Origin';
399 end if;
400 close c_ethnic;
401 Commented for bug#11736960 ends */
402 --
403
404 open c_assignment;
405 fetch c_assignment into l_exists;
406 if c_assignment%notfound then
407 l_counted := 'Y';
408 if l_ex_reason is not null then
409 l_ex_reason := l_ex_reason||', Assignment is not of reporting type';
410 else
411 l_ex_reason := 'Assignment is not of reporting type';
412 end if;
413 end if;
414 close c_assignment;
415
416 open c_emp_category;
417 fetch c_emp_category into l_exists;
418 if c_emp_category%notfound then
419 l_counted := 'Y';
420 if l_ex_reason is not null then
421 l_ex_reason := l_ex_reason||', Employment category is not of reporting category';
422 else
423 l_ex_reason := 'Employment category is not of reporting category';
424 end if;
425 end if;
426 close c_emp_category;
427
428 open c_job_category;
429 fetch c_job_category into l_exists;
430 if c_job_category%notfound then
431 l_counted := 'Y';
432 if l_ex_reason is not null then
433 l_ex_reason := l_ex_reason||', Job category is not of EEO-1 category';
434 else
435 l_ex_reason := 'Job category is not of EEO-1 category';
436 end if;
437 end if;
438 close c_job_category;
439
440 open c_eeo1_extra_info;
441 fetch c_eeo1_extra_info into l_exists;
442 if c_eeo1_extra_info%NOTFOUND then
443 l_counted := 'Y';
444 if l_ex_reason is not null then
445 l_ex_reason := l_ex_reason||', Loc has no EEO-1 Extra Information Type';
446 else
447 l_ex_reason := 'Loc has no EEO-1 Extra Information Type';
448 end if;
449 end if;
450 close c_eeo1_extra_info;
451
452 -- Added for bug#11736960
453 /* Check if Two or More race field is set to 'Yes' and One or No individual
454 race field is set to 'Yes' */
455 BEGIN
456 SELECT 'Y'
457 INTO l_two_or_more
458 FROM per_people_extra_info
459 WHERE person_id = person_id1
460 AND information_type = 'US_ETHNIC_ORIGIN'
461 AND pei_information7 = 'Y'
462 AND (DECODE(pei_information1, 'Y', 1, 0)
463 + DECODE(pei_information2, 'Y', 1, 0)
464 + DECODE(pei_information3, 'Y', 1, 0)
465 + DECODE(pei_information4, 'Y', 1, 0)
466 + DECODE(pei_information5, 'Y', 1, 0)
467 + DECODE(pei_information6, 'Y', 1, 0)
468 ) <= 1;
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 NULL;
472 END;
473
474 IF l_two_or_more = 'Y' THEN
475 IF l_ex_reason IS NOT NULL THEN
476 l_ex_reason := l_ex_reason ||', ''Two or More Race'' field is set to ''Yes'' ' ||
477 'and only one or no individual race field is set to ''Yes'' in Ethnic Origin(EIT)';
478 ELSE
479 l_ex_reason := 'WARNING: ''Two or More Race'' field is set to ''Yes'' ' ||
480 'and only one or no individual race field is set to ''Yes'' in Ethnic Origin(EIT). ' ||
481 'However the employee gets counted in EEO-1 report under ''Hispanic/Latino'' or ''Two or More races''';
482 END IF;
483 END IF;
484
485 cp_emp_name := null;
486 cp_emp_num := null;
487 cp_gender := null;
488 cp_job_cat := null;
489 cp_ethnic := null;
490 cp_emp_cat := null;
491 cp_ass_type := null;
492 cp_reason := null;
493 if l_ex_reason is not null then
494 cp_reason := l_ex_reason;
495
496 open c_person_name;
497 fetch c_person_name into cp_emp_name
498 ,cp_emp_num
499 ,cp_gender;
500 if c_person_name%notfound or cp_emp_name is null then
501 l_ex_reason := 'data not found for name, employee number or gender';
502 end if;
503 close c_person_name;
504
505 open c_job_cat;
506 fetch c_job_cat into cp_job_cat;
507 if c_job_cat%notfound then
508 l_ex_reason := 'data not found for job or job category';
509
510 cp_job_cat := 'Not Specified';
511
512 end if;
513 close c_job_cat;
514
515 cp_ethnic := NVL(hr_general.decode_lookup('US_ETHNIC_GROUP', l_ethnic_origin),
516 'Not Specified');
517
518 /* Commented for bug#11736960 starts
519 open c_eth_cat;
520 fetch c_eth_cat into cp_ethnic;
521 if c_eth_cat%notfound or cp_ethnic is null then
522 l_ex_reason := 'data not found for ethnic category';
523 end if;
524 close c_eth_cat;
525 Commented for bug#11736960 ends */
526
527 open c_emp_cat;
528 fetch c_emp_cat into cp_emp_cat;
529 if c_emp_cat%notfound or cp_emp_cat is null then
530 l_ex_reason := 'data not found for emp category';
531 end if;
532 close c_emp_cat;
533
534 open c_ass_type;
535 fetch c_ass_type into cp_ass_type;
536 if c_ass_type%notfound or cp_ass_type is null then
537 l_ex_reason := 'data not found for assignment type';
538 end if;
539 close c_ass_type;
540
541 cp_no_rows := cp_no_rows + 1;
542 --PER_PERUSEOX_XMLP_PKG.cp_display := PER_PERUSEOX_XMLP_PKG.cp_display + 1;
543 cp_display := cp_display + 1;
544
545 --RAISE_APPLICATION_ERROR(-20001,'cp_display'||cp_display) ;
546
547 --auto_trans(cp_display);
548 if P_AUDIT_REPORT = 'Y' then
549 l_buffer := person_id1 || g_delimiter ||
550 cp_emp_name || g_delimiter ||
551 cp_emp_num || g_delimiter ||
552 cp_gender || g_delimiter ||
553 cp_ethnic || g_delimiter ||
554 cp_emp_cat || g_delimiter ||
555 cp_ass_type || g_delimiter ||
556 location_id || g_delimiter ||
557 replace(address1,',',' ') || g_delimiter ||
558 replace(cp_reason,',',';') ||
559 g_eol;
560 -- file_io.put(l_buffer);
561 end if;
562 end if;
563 return(null);
564 end;
565
566 --Functions to refer Oracle report placeholders--
567
568 Function CP_no_rows_p return number is
569 Begin
570 return CP_no_rows;
571 END;
572 Function CP_Emp_Name_p return varchar2 is
573 Begin
574 return CP_Emp_Name;
575 END;
576 Function CP_Emp_Num_p return varchar2 is
577 Begin
578 return CP_Emp_Num;
579 END;
580 Function CP_Gender_p return varchar2 is
581 Begin
582 return CP_Gender;
583 END;
584 Function CP_Location_p return varchar2 is
585 Begin
586 return CP_Location;
587 END;
588 Function CP_Job_Cat_p return varchar2 is
589 Begin
590 return CP_Job_Cat;
591 END;
592 Function CP_Ethnic_p return varchar2 is
593 Begin
594 return CP_Ethnic;
595 END;
596 Function CP_Emp_Cat_p return varchar2 is
597 Begin
598 return CP_Emp_Cat;
599 END;
600 Function CP_ass_type_p return varchar2 is
601 Begin
602 return CP_ass_type;
603 END;
604 Function CP_Reason_p return varchar2 is
605 Begin
606 return CP_Reason;
607 END;
608 Function CP_display_p return number is
609 Begin
610 -- return nvl(PER_PERUSEOX_XMLP_PKG.CP_display,0);
611 return nvl(CP_display,0);
612 END;
613 Function c_business_group_name_p return varchar2 is
614 Begin
615 return c_business_group_name;
616 END;
617 Function c_hierarchy_name_p return varchar2 is
618 Begin
619 return c_hierarchy_name;
620 END;
621 Function c_hierarchy_version_num_p return number is
622 Begin
623 return c_hierarchy_version_num;
624 END;
625 Function c_parent_org_id_p return number is
626 Begin
627 return c_parent_org_id;
628 END;
629 Function c_parent_node_id_p return number is
630 Begin
631 return c_parent_node_id;
632 END;
633 Function c_ass_loc_p return number is
634 Begin
635 return c_ass_loc;
636 END;
637 Function c_report_date_p return date is
638 Begin
639 return c_report_date;
640 END;
641 Function c_report_year_p return varchar2 is
642 Begin
643 return c_report_year;
644 END;
645 END PER_PERUSEOX_XMLP_PKG ;
[Home] [Help]