1 PACKAGE BODY PER_PERUSEOX_XMLP_PKG AS
2 /* $Header: PERUSEOXB.pls 120.1 2007/12/31 09:15:06 amakrish noship $ */
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 cursor c_location is
153 select 'Y'
154 from
155 per_all_assignments_f ass
156 where
157 ass.person_id = person_id1
158 and report_date_end between ass.effective_start_date
159 and ass.effective_end_date
160 and ass.assignment_type = 'E'
161 and ass.primary_flag = 'Y'
162 and (ass.location_id is null
163 or ass.location_id not in
164 (select entity_id
165 from per_gen_hierarchy_nodes
166 where hierarchy_version_id = p_hierarchy_version_id
167 and node_type <> 'PAR'))
168 ;
169 cursor c_assignment is
170 select 'Y'
171 from
172 per_all_assignments_f ass
173 where
174 ass.person_id = person_id1
175 and ass.business_group_id = P_BUSINESS_GROUP_ID
176 and report_date_end between ass.effective_start_date and ass.effective_end_date
177
178 and ass.assignment_type = 'E'
179 and ass.primary_flag = 'Y'
180 and exists (select 'x'
181 from hr_organization_information hoi1
182 where to_char(ass.assignment_status_type_id) = hoi1.org_information1
183 and hoi1.org_information_context = 'Reporting Statuses'
184 and hoi1.organization_id = P_BUSINESS_GROUP_ID);
185 cursor c_ethnic is
186 select 'Y'
187 from per_all_people_f peo
188 where
189 (
190 (peo.per_information1 is not null
191 and exists
192 (select null
193 from hr_lookups
194 where peo.per_information1 = lookup_code
195 and lookup_type = 'US_ETHNIC_GROUP'
196 )
197 )
198 )
199 and peo.person_id = person_id1
200 and peo.per_information_category = 'US'
201 and peo.business_group_id = P_BUSINESS_GROUP_ID
202 and report_date_end between peo.effective_start_date
203 and peo.effective_end_date
204 ;
205 cursor c_emp_category is
206 select 'Y'
207 from
208 per_all_assignments_f ass
209 where
210 ass.person_id = person_id1
211 and ass.business_group_id = P_BUSINESS_GROUP_ID
212 and report_date_end between ass.effective_start_date
213 and ass.effective_end_date
214
215 and ass.assignment_type = 'E'
216 and ass.primary_flag = 'Y'
217 and ass.employment_category is not null
218 and exists (select 'x'
219 from hr_organization_information hoi2
220 where ass.employment_category = hoi2.org_information1
221 and hoi2.org_information_context = 'Reporting Categories'
222 and hoi2.organization_id = P_BUSINESS_GROUP_ID);
223 cursor c_job_category is
224 select 'Y'
225 from
226 per_all_assignments_f ass
227 ,per_jobs job
228 where
229 ass.person_id = person_id1
230 and ass.business_group_id = P_BUSINESS_GROUP_ID
231 and job.business_group_id = P_BUSINESS_GROUP_ID
232 and report_date_end between ass.effective_start_date
233 and ass.effective_end_date
234
235 and ass.assignment_type = 'E'
236 and ass.primary_flag = 'Y'
237 and ass.job_id is not null
238 and ass.job_id = job.job_id
239 and job.job_information_category = 'US'
240 and report_date_end between job.date_from
241 and nvl(job.date_to, report_date_end)
242 and job.job_information1 in
243 (select lookup_code
244 from hr_lookups
245 where lookup_type = 'US_EEO1_JOB_CATEGORIES')
246 ;
247 cursor c_person_name is
248 select substr(peo.full_name,1,150) emp_name,
249 peo.employee_number emp_num,
250 peo.sex sex
251 from per_all_people_f peo
252 where peo.person_id = person_id1
253 and peo.business_group_id = P_BUSINESS_GROUP_ID
254 and report_date_end between peo.effective_start_date
255 and peo.effective_end_date
256
257 ;
258 cursor c_job_cat is
259 select nvl(jbt.name, 'Not Specified')||' '||nvl(lup.meaning,'') job_cat
260 from hr_lookups lup
261 ,per_all_assignments_f ass
262 ,per_jobs_vl job
263 ,per_jobs_tl jbt
264 where ass.person_id = person_id1
265
266 and report_date_end between ass.effective_start_date
267 and ass.effective_end_date
268 and report_date_end between job.date_from
269 and nvl(job.date_to, report_date_end)
270 and ass.assignment_type = 'E'
271 and ass.primary_flag = 'Y'
272 and ass.business_group_id = P_BUSINESS_GROUP_ID
273 and job.business_group_id = P_BUSINESS_GROUP_ID
274 and ass.job_id = jbt.job_id (+)
275 and jbt.language(+) = userenv('LANG')
276 and ass.job_id = job.job_id (+)
277 and job.job_information1 = lup.lookup_code(+)
278 and lup.lookup_type(+) = 'US_EEO1_JOB_CATEGORIES'
279 and job.job_information_category(+) = 'US' ;
280 cursor c_eth_cat is
281 select nvl(lup.meaning, 'Not Specified') ethnic
282 from hr_lookups lup
283 ,per_all_people_f peo
284 where peo.person_id = person_id1
285 and peo.business_group_id = P_BUSINESS_GROUP_ID
286 and peo.per_information_category(+) = 'US'
287 and peo.per_information1 = lup.lookup_code(+)
288 and lup.lookup_type(+) = 'US_ETHNIC_GROUP'
289 and report_date_end between peo.effective_start_date
290 and peo.effective_end_date
291
292 ;
293 cursor c_emp_cat is
294 select nvl(lup.meaning, 'Not Specified') emp_cat
295 from hr_lookups lup
296 ,per_all_assignments_f ass
297 where ass.person_id = person_id1
298 and ass.business_group_id = P_BUSINESS_GROUP_ID
299 and ass.employment_category = lup.lookup_code(+)
300 and lup.lookup_type(+) = 'EMP_CAT'
301
302 and report_date_end between ass.effective_start_date
303 and ass.effective_end_date
304 and ass.assignment_type = 'E'
305 and ass.primary_flag = 'Y';
306 cursor c_ass_type is
307 select past.user_status ustat
308 from per_all_assignments_f ass
309 ,per_assignment_status_types past
310 where ass.person_id = person_id1
311 and ass.business_group_id = P_BUSINESS_GROUP_ID
312
313 and report_date_end between ass.effective_start_date
314 and ass.effective_end_date
315 and ass.assignment_type = 'E'
316 and ass.primary_flag = 'Y'
317 and ass.assignment_status_type_id = past.assignment_status_type_id
318 and past.active_flag = 'Y'
319 and past.primary_flag = 'P'
320 ;
321 cursor c_eeo1_extra_info is
322 select 'Y'
323 from
324 per_all_assignments_f ass
325 ,hr_location_extra_info hlei1
326 ,hr_location_extra_info hlei2
327 where
328 ass.person_id = person_id1
329 and report_date_end between ass.effective_start_date
330 and ass.effective_end_date
331 and ass.assignment_type = 'E'
332 and ass.primary_flag = 'Y'
333 and to_char(hlei1.location_id) = ass.location_id
334 and to_char(hlei2.location_id) = ass.location_id
335 and hlei1.location_id = hlei2.location_id
336 and hlei1.information_type = 'EEO-1 Specific Information'
337 and hlei1.lei_information_category= 'EEO-1 Specific Information'
338 and hlei2.information_type = 'Establishment Information'
339 and hlei2.lei_information_category= 'Establishment Information';
340
341 begin
342
343
344 if ASS_LOC <> c_ass_loc then
345 cp_display := 0;
346 end if;
347
348 c_ass_loc := ASS_LOC;
349 open c_location;
350 fetch c_location into l_exists;
351 if c_location%found then
352 l_counted := 'Y';
353 l_ex_reason := 'Loc not in hierarchy';
354 end if;
355 close c_location;
356 open c_ethnic;
357 fetch c_ethnic into l_exists;
358 if c_ethnic%notfound then
359 l_counted := 'Y';
360 if l_ex_reason is not null then
361 l_ex_reason := l_ex_reason||', No Ethnic Origin';
362 else
363 l_ex_reason := 'No Ethnic Origin';
364 end if;
365 end if;
366 close c_ethnic;
367 open c_assignment;
368 fetch c_assignment into l_exists;
369 if c_assignment%notfound then
370 l_counted := 'Y';
371 if l_ex_reason is not null then
372 l_ex_reason := l_ex_reason||', Assignment is not of reporting type';
373 else
374 l_ex_reason := 'Assignment is not of reporting type';
375 end if;
376 end if;
377 close c_assignment;
378 open c_emp_category;
379 fetch c_emp_category into l_exists;
380 if c_emp_category%notfound then
381 l_counted := 'Y';
382 if l_ex_reason is not null then
383 l_ex_reason := l_ex_reason||', Employment category is not of reporting category';
384 else
385 l_ex_reason := 'Employment category is not of reporting category';
386 end if;
387 end if;
388 close c_emp_category;
389 open c_job_category;
390 fetch c_job_category into l_exists;
391 if c_job_category%notfound then
392 l_counted := 'Y';
393 if l_ex_reason is not null then
394 l_ex_reason := l_ex_reason||', Job category is not of EEO-1 category';
395 else
396 l_ex_reason := 'Job category is not of EEO-1 category';
397 end if;
398 end if;
399 close c_job_category;
400 open c_eeo1_extra_info;
401 fetch c_eeo1_extra_info into l_exists;
402 if c_eeo1_extra_info%NOTFOUND then
403 l_counted := 'Y';
404 if l_ex_reason is not null then
405 l_ex_reason := l_ex_reason||', Loc has no EEO-1 Extra Information Type';
406 else
407 l_ex_reason := 'Loc has no EEO-1 Extra Information Type';
408 end if;
409 end if;
410 close c_eeo1_extra_info;
411
412
413 cp_emp_name := null;
414 cp_emp_num := null;
415 cp_gender := null;
416 cp_job_cat := null;
417 cp_ethnic := null;
418 cp_emp_cat := null;
419 cp_ass_type := null;
420 cp_reason := null;
421 if l_ex_reason is not null then
422 cp_reason := l_ex_reason;
423 open c_person_name;
424 fetch c_person_name into cp_emp_name
425 ,cp_emp_num
426 ,cp_gender;
427 if c_person_name%notfound or cp_emp_name is null then
428 l_ex_reason := 'data not found for name, employee number or gender';
429 end if;
430 close c_person_name;
431 open c_job_cat;
432 fetch c_job_cat into cp_job_cat;
433 if c_job_cat%notfound then
434 l_ex_reason := 'data not found for job or job category';
435
436 cp_job_cat := 'Not Specified';
437
438 end if;
439 close c_job_cat;
440
441 open c_eth_cat;
442 fetch c_eth_cat into cp_ethnic;
443 if c_eth_cat%notfound or cp_ethnic is null then
444 l_ex_reason := 'data not found for ethnic category';
445 end if;
446 close c_eth_cat;
447 open c_emp_cat;
448 fetch c_emp_cat into cp_emp_cat;
449 if c_emp_cat%notfound or cp_emp_cat is null then
450 l_ex_reason := 'data not found for emp category';
451 end if;
452 close c_emp_cat;
453 open c_ass_type;
454 fetch c_ass_type into cp_ass_type;
455 if c_ass_type%notfound or cp_ass_type is null then
456 l_ex_reason := 'data not found for assignment type';
457 end if;
458 close c_ass_type;
459 cp_no_rows := cp_no_rows + 1;
460 --PER_PERUSEOX_XMLP_PKG.cp_display := PER_PERUSEOX_XMLP_PKG.cp_display + 1;
461 cp_display := cp_display + 1;
462
463 --RAISE_APPLICATION_ERROR(-20001,'cp_display'||cp_display) ;
464
465 --auto_trans(cp_display);
466 if P_AUDIT_REPORT = 'Y' then
467 l_buffer := person_id1 || g_delimiter ||
468 cp_emp_name || g_delimiter ||
469 cp_emp_num || g_delimiter ||
470 cp_gender || g_delimiter ||
471 cp_ethnic || g_delimiter ||
472 cp_emp_cat || g_delimiter ||
473 cp_ass_type || g_delimiter ||
474 location_id || g_delimiter ||
475 replace(address1,',',' ') || g_delimiter ||
476 replace(cp_reason,',',';') ||
477 g_eol;
478 -- file_io.put(l_buffer);
479 end if;
480 end if;
481 return(null);
482 end;
483
484 --Functions to refer Oracle report placeholders--
485
486 Function CP_no_rows_p return number is
487 Begin
488 return CP_no_rows;
489 END;
490 Function CP_Emp_Name_p return varchar2 is
491 Begin
492 return CP_Emp_Name;
493 END;
494 Function CP_Emp_Num_p return varchar2 is
495 Begin
496 return CP_Emp_Num;
497 END;
498 Function CP_Gender_p return varchar2 is
499 Begin
500 return CP_Gender;
501 END;
502 Function CP_Location_p return varchar2 is
503 Begin
504 return CP_Location;
505 END;
506 Function CP_Job_Cat_p return varchar2 is
507 Begin
508 return CP_Job_Cat;
509 END;
510 Function CP_Ethnic_p return varchar2 is
511 Begin
512 return CP_Ethnic;
513 END;
514 Function CP_Emp_Cat_p return varchar2 is
515 Begin
516 return CP_Emp_Cat;
517 END;
518 Function CP_ass_type_p return varchar2 is
519 Begin
520 return CP_ass_type;
521 END;
522 Function CP_Reason_p return varchar2 is
523 Begin
524 return CP_Reason;
525 END;
526 Function CP_display_p return number is
527 Begin
528 -- return nvl(PER_PERUSEOX_XMLP_PKG.CP_display,0);
529 return nvl(CP_display,0);
530 END;
531 Function c_business_group_name_p return varchar2 is
532 Begin
533 return c_business_group_name;
534 END;
535 Function c_hierarchy_name_p return varchar2 is
536 Begin
537 return c_hierarchy_name;
538 END;
539 Function c_hierarchy_version_num_p return number is
540 Begin
541 return c_hierarchy_version_num;
542 END;
543 Function c_parent_org_id_p return number is
544 Begin
545 return c_parent_org_id;
546 END;
547 Function c_parent_node_id_p return number is
548 Begin
549 return c_parent_node_id;
550 END;
551 Function c_ass_loc_p return number is
552 Begin
553 return c_ass_loc;
554 END;
555 Function c_report_date_p return date is
556 Begin
557 return c_report_date;
558 END;
559 Function c_report_year_p return varchar2 is
560 Begin
561 return c_report_year;
562 END;
563 END PER_PERUSEOX_XMLP_PKG ;
[Home] [Help]