1 PACKAGE BODY per_ca_emp_equity_pkg AS
2 /* $Header: perhrcaempequity.pkb 120.0 2006/05/25 06:36:55 ssmukher noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : per_ca_emp_equity_pkg
21
22 Description : This package is used for generating the employee.txt
23 promot.txt ,term.txt and excep.txt tab delimited
24 text file for Employment Equity report.
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------ ------- --------------------------
30 28-Apr-2005 ssmukher 115.0 Created.
31 04-Jul-2005 ssmukher 115.1 Removed the cursor cur_term_asg
32 Modified the CMA code values in the
33 cursor cur_emp_cma, added another delete
34 stmt at the start of the procedure to
35 remove records from per_ca_ee_report_lines
36 table,added an additional check before
37 inserting temporary employees as well as
38 Promoted employee details.
39 05-Jul-2005 ssmukher 115.2 Modified the cursor c_promo.
40 Modified the effective end date condition for
41 per_all_people_f and per_all_assignments_f
42 table
43 05-Jul-2005 ssmukher 115.3 Modified the code so that if the designated
44 code is not specified for an employee then
45 record is not inserted into the exception
46 report
47 07-Jul-2005 ssmukher Added check for secure user.
48 08-Jul-2005 ssmukher 115.4 Fix for Bug 4480102
49 13-Jul-2005 ssmukher 115.5 Bug 4488375 :Modified the length of Last name
50 and First name in cur_emp_categ_person cursor
51 to 20 and 15 respectively.
52 14-Jul-2005 ssmukher 115.6 Bug 4490792 : Modified the cursor c_total_salary
53 Bug 4493278 fix is also included in it.
54 20-Jul-2005 ssmukher 115.7 4501549 Modified the cursor cur_emp_categ_naic_asg and
55 cur_emp_categ_asg to include the maximum effective
56 start date check.
57 27-Jul-2005 ssmukher 115.8 4500929 Modified the cursor c_total_salary.
58 ******************************************************************************/
59
60 /********* Procedure to create the employee.txt file ***************/
61 /**************** Start of Procedure ******************************/
62
63 v_person_type_temp person_type_tab;
64 v_person_type person_type_tab;
65 v_job_id_temp job_id_tab;
66 v_job_id job_id_tab;
67 v_job_noc_temp job_noc_tab;
68 v_job_noc job_noc_tab;
69
70 /* Function for checking if a particular job id exists*/
71 FUNCTION job_exists (p_job_id IN NUMBER)
72 RETURN VARCHAR2 IS
73 BEGIN
74 IF v_job_id.COUNT > 0 THEN
75 IF v_job_id.EXISTS(p_job_id) THEN
76 RETURN v_job_noc(p_job_id);
77 END IF;
78 END IF;
79
80 RETURN NULL;
81
82 END job_exists;
83
84 /* Function for checking if the person type exists */
85 FUNCTION person_type_exists (p_person_type IN NUMBER)
86 RETURN VARCHAR2 IS
87 BEGIN
88 IF v_person_type.COUNT > 0 THEN
89 IF v_person_type.EXISTS(p_person_type) THEN
90 RETURN 'Y';
91 END IF;
92 END IF;
93
94 RETURN NULL;
95
96 END person_type_exists;
97
98 /* Procedure for printing employee details */
99 procedure employee_dtls (errbuf out nocopy varchar2,
100 retcode out nocopy number,
101 p_business_group_id in number,
102 p_year in varchar2,
103 p_naic_code in varchar2)
104 is
105 /* Initialising the variables */
106 v_year_start date;
107 v_year_end date ;
108 l_file_name varchar2(50);
109
110 /*Cursor for checking the validity of Job code assigned to an employee */
111 cursor cur_jobs is
112 select job_id,
113 job_information7
114 from per_jobs,
115 hr_lookups
116 where lookup_type = 'EEOG'
117 and upper(ltrim(rtrim(lookup_code)))
118 =upper(ltrim(rtrim(job_information1)))
119 and upper(ltrim(rtrim(job_information_category))) = 'CA'
120 and business_group_id = p_business_group_id;
121
122 /*Cursor for checking the validity of person type for an employee */
123 cursor cur_person_types is
124 select person_type_id
125 from per_person_types
126 where upper(ltrim(rtrim(system_person_type)))='EMP'
127 and business_group_id = p_business_group_id;
128
129 /*Cursor for checking the NAIC code validation */
130 cursor cur_naic_code (p_keyflex_id number) is
131 select hl.lookup_code
132 from hr_lookups hl,
133 hr_soft_coding_keyflex hsck
134 where hsck.soft_coding_keyflex_id = p_keyflex_id and
135 hl.lookup_type = 'NAIC' and
136 (
137 (
138 hsck.segment6 is not null and
139 hl.lookup_code = hsck.segment6
140 )
141 OR
142 (
143 hsck.segment6 is null and
144 exists
145 ( select 1
146 from hr_organization_information hoi
147 where hoi.org_information8 is not null and
148 hl.lookup_code=hoi.org_information8 and
149 hsck.segment1 = to_char(hoi.organization_id) and
150 hoi.org_information_context = 'Canada Employer Identification'
151 )
152 )
153 );
154
155 /* Cursor for fetching the distinct person id and person deatils for Permanent,Temporary employees
156 with NAIC code not been specified in the concurrent program */
157
158 cursor cur_emp_categ_person ( p_start_date date) is
159 select
160 distinct(ppf.person_id) l_person_id,
161 ppf.employee_number emp_no,
162 substr(ppf.first_name,1,15) first_name,
163 substr(ppf.last_name,1,20) last_name,
164 ppf.sex gender,
165 nvl(ppf.per_information5,'N') desg_abor,
166 nvl(ppf.per_information6,'N') desg_vminor,
167 nvl(ppf.per_information7,'N') desg_disab,
168 trunc(ppf.original_date_of_hire) date_of_hire
169 from
170 per_all_people_f ppf
171 where person_type_exists(ppf.person_type_id) is not null and
172 ppf.effective_end_date >= p_start_date and
173 ppf.business_group_id = p_business_group_id
174 order by l_person_id,emp_no;
175
176 /* Cursor for fetching all the primary assignment details corresponding to the person fetched
177 from the cursor cur_emp_categ_person*/
178 /* Bug 4501549 Added the check for maximum effective start date */
179 cursor cur_emp_categ_asg ( p_person_id number,
180 p_start_date date) is
181 select paf.assignment_id asg_id,
182 job_exists(paf.job_id) noc_code,
183 decode (substr(NVL(paf.employment_category,'-1'),1,2),'FR','01','PR','02','PT','03','FT','03','-1','-1','04') employment_category,
184 paf.location_id loc_id,
185 paf.soft_coding_keyflex_id flex_id,
186 paf.effective_start_date st_dt
187 from
188 per_all_assignments_f paf
189 where paf.person_id = p_person_id and
190 paf.effective_end_date >= p_start_date and
191 paf.business_group_id = p_business_group_id and
192 paf.primary_flag = 'Y' and
193 job_exists(paf.job_id) is not null and
194 paf.effective_start_date = (select max(effective_start_date)
195 from per_all_assignments_f paf1
196 where paf1.business_group_id = p_business_group_id
197 and paf1.assignment_id = paf.assignment_id
198 and paf1.person_id = p_person_id);
199
200 /* Cursor for fetching all the primary assignment details corresponding to the person fetched
201 from the cursor cur_emp_categ_naic_person */
202 /* Bug 4501549 Added the check for maximum effective start date */
203
204 cursor cur_emp_categ_naic_asg ( p_person_id number,
205 p_start_date date ) is
206 select paf.assignment_id asg_id,
207 job_exists(paf.job_id) noc_code,
208 decode (substr(NVL(paf.employment_category,'-1'),1,2),'FR','01','PR','02','PT','03','FT','03','-1','-1','04') employment_category,
209 paf.location_id loc_id,
210 paf.effective_start_date st_dt
211 from
212 per_all_assignments_f paf,
213 hr_soft_coding_keyflex hsck,
214 hr_lookups hl
215 where paf.person_id = p_person_id and
216 paf.business_group_id = p_business_group_id and
217 paf.effective_end_date >= p_start_date and
218 paf.primary_flag = 'Y' and
219 job_exists(paf.job_id) is not null and
220 hl.lookup_type = 'NAIC' and
221 paf.effective_start_date = (select max(effective_start_date)
222 from per_all_assignments_f paf1
223 where paf1.business_group_id = p_business_group_id
224 and paf1.assignment_id = paf.assignment_id
225 and paf1.person_id = p_person_id) and
226 hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
227 (
228 (
229 hsck.segment6 is not null and
230 hl.lookup_code = hsck.segment6 and
231 hl.lookup_code = p_naic_code
232 )
233 OR
234 (
235 hsck.segment6 is null and
236 exists
237 ( select 1
238 from hr_organization_information hoi
239 where hoi.org_information8 is not null and
240 hl.lookup_code=hoi.org_information8 and
241 hsck.segment1 = to_char(hoi.organization_id) and
242 hoi.org_information8 = p_naic_code and
243 hoi.org_information_context = 'Canada Employer Identification'
244 )
245 )
246 );
247
248
249 /* Cursor to fetch the terminated Permanent as well as temporary employees person id and details with
250 the NAIC code not been specified in the concurrent request parameter */
251
252 cursor cur_term_date ( p_person_id number,
253 p_start_date date,
254 p_end_date date) is
255 select trunc(ppos.actual_termination_date) term_date,
256 trunc(ppos.projected_termination_date) end_dt
257 from
258 per_all_people_f ppf,
259 per_periods_of_service ppos
260 where person_type_exists(ppf.person_type_id) is not null and
261 ppos.actual_termination_date between
262 ppf.effective_start_date and
263 ppf.effective_end_date and
264 ppf.business_group_id=p_business_group_id and
265 ppf.person_id = p_person_id and
266 ppf.person_id=ppos.person_id and
267 ppos.actual_termination_date is not null and
268 ppos.actual_termination_date >= p_start_date and
269 ppos.actual_termination_date <= p_end_date;
270
271 /* Term dare details for the Temporary Employees */
272 cursor cur_temp_term_date ( p_person_id number,
273 p_start_date date,
274 p_end_date date) is
275 select max(paf.effective_start_date) start_dt,
276 trunc(ppos.actual_termination_date) term_date,
277 trunc(ppos.projected_termination_date) end_dt
278 from
279 per_all_people_f ppf,
280 per_all_assignments_f paf,
281 per_periods_of_service ppos
282 where person_type_exists(ppf.person_type_id) is not null and
283 paf.person_id = ppf.person_id and
284 ppos.actual_termination_date between
285 ppf.effective_start_date and
286 ppf.effective_end_date and
287 ppos.actual_termination_date between
288 paf.effective_start_date and
289 paf.effective_end_date and
290 paf.effective_end_date = ppos.actual_termination_date and
291 ppf.business_group_id=p_business_group_id and
292 ppf.person_id = p_person_id and
293 ppf.person_id=ppos.person_id and
294 ppos.actual_termination_date is not null and
295 ppos.actual_termination_date >= p_start_date and
296 ppos.actual_termination_date <= p_end_date
297 group by trunc(ppos.actual_termination_date) ,
298 trunc(ppos.projected_termination_date);
299
300
301 /* Cursor to fetch the Salary for the employee */
302 /* Bug 4500929 fix : Modified the query for checking the maximum effective start date
303 for the assignment */
304 cursor c_total_salary (p_person_id number) is
305 select sum(trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor) salary
306 from per_pay_bases ppb,
307 per_all_assignments_f paf,
308 per_all_people_f ppf,
309 per_pay_proposals_v2 pppv,
310 per_assignment_status_types past,
311 hr_lookups hrl
312 where paf.pay_basis_id = ppb.pay_basis_id and
313 paf.person_id=ppf.person_id and
314 past.assignment_status_type_id = paf.assignment_status_type_id and
315 pppv.assignment_id = paf.assignment_id and
316 hrl.lookup_type ='PER_ASS_SYS_STATUS' and
317 hrl.lookup_code = past.per_system_status and
318 hrl.lookup_code = 'ACTIVE_ASSIGN' and
319 ppf.current_emp_or_apl_flag = 'Y' and
320 ppb.business_group_id = p_business_group_id and
321 paf.person_id = p_person_id and
322 paf.assignment_id=pppv.assignment_id and
323 paf.effective_start_date = ( select max(effective_start_date)
324 from per_all_assignments_f paf1,
325 per_assignment_status_types past1,
326 hr_lookups hrl1
327 where paf1.business_group_id = p_business_group_id
328 and paf1.assignment_id = pppv.assignment_id
329 and paf1.person_id = p_person_id
330 and past1.assignment_status_type_id = paf1.assignment_status_type_id
331 and hrl1.lookup_type ='PER_ASS_SYS_STATUS'
335 ppf.effective_start_date = ( select max(effective_start_date)
332 and hrl1.lookup_code = past1.per_system_status
333 and hrl1.lookup_code = 'ACTIVE_ASSIGN'
334 ) and
336 from per_all_people_f ppf1
337 where ppf1.person_id = ppf.person_id
338 and ppf1.business_group_id = p_business_group_id
339 and ppf1.current_emp_or_apl_flag = 'Y'
340 ) and
341 pppv.change_date = (select max(pppv2.change_date)
342 from per_pay_proposals_v2 pppv2
343 where pppv2.assignment_id = paf.assignment_id
344 and pppv2.change_date <= v_year_end
345 )
346 group by ppf.person_id;
347
348 /* Cursor to fetch the Province code for the employee */
349 cursor cur_emp_prov (p_location_code number) is
350 select decode( hl1.lookup_code,'ON','10',
351 'QC','11',
352 'NS','12',
353 'NB','13',
354 'MB','14',
355 'BC','15',
356 'PE','16',
357 'SK','17',
358 'AB','18',
359 'NF','19',
360 'YT','20',
361 'NT','21',
362 'NU','22',
363 '98')
364 from hr_locations_all hloc,
365 hr_lookups hl1
366 where hloc.location_id = p_location_code
367 and hl1.lookup_code = hloc.region_1
368 and hl1.lookup_type = 'CA_PROVINCE';
369
370 /* Cursor to fetch the CMA code for the employee */
371 cursor cur_emp_cma (p_location_code number) is
372 SELECT decode(ltrim(rtrim(hl1.lookup_code)),
373 'CALGARY' ,'01', 'EDMONTON','02','HALIFAX','03',
374 'MONTREAL','04', 'REGINA','05', 'TORONTO','06',
375 'VANCOUVER','07', 'WINNEPEG','08' ,'ST JOHNS','50',
376 'SAINT JOHN','52','CHICOUTIMI','53',
377 'QUEBEC','54','SHERBROOKE','55','TROIS RIVIERES','56',
378 'KINGSTON NEW','57','OTTOWA HULL','58','OSHAWA','59',
379 'HAMILTON','61','ST CATHARINES NIAGARA','62',
380 'KITCHENER','63','LONDON','64','WINDSOR','65','SUDBURY','66',
381 'THUNDER BAY','67','SASKATOON','70','ABBOTSFORD NEW','72',
382 'VICTORIA','74','ALBERTA LESS CMA','85',
383 'BRITISH COLUMBIA LESS CMA','86','MANITOBA LESS CMA','87',
384 'NEW BRUNSWICK LESS CMA','88','NOVA SCOTIA LESS CMA','89',
385 'NORTH WEST TERRITORIES','90','NEWFOUNDLAND LESS CMA','91',
386 'NUNAVUT','92','ONTORIA LESS CMA','93','PRINCE EDWARD ISLAND','94',
387 'QUEBEC LESS CMA','95','SASKATCHEWAN LESS CMA','96','YUKON TERRITORY','97')
388 FROM hr_lookups hl1,
389 hr_locations_all hloc
390 WHERE hloc.location_id = p_location_code
391 AND hloc.region_2 = hl1.lookup_code
392 AND hl1.lookup_type = 'CA_CMA';
393
394 /* Cursor for the counting the number of promotion */
395 cursor c_promo ( p_person_id number,
396 p_start_date date,
397 p_end_date date) is
398 select distinct ppp.assignment_id asg_id,
399 TRUNC(ppp.change_date) promo_date
400 from per_pay_proposals_v2 ppp,
401 per_all_people_f ppf,
402 per_all_assignments_f paf
403 where ppf.person_id = paf.person_id and
404 ppf.person_id = p_person_id and
405 paf.assignment_id = ppp.assignment_id and
406 ppf.effective_end_date > p_start_date and
407 paf.effective_end_date > p_start_date and
408 ppp.business_group_id = p_business_group_id and
409 ppp.proposal_reason = 'PROM' and
410 ppp.change_date BETWEEN p_start_date AND p_end_date and
411 ppp.approved = 'Y';
412
413 /* Cursor to check if the user isa secure user or not */
414 cursor c_person_exists( p_person_id number) is
415 select 'Y'
416 from per_people_f
417 where person_id = p_person_id;
418
419 /*Declaration of local variables */
420 table_date per_fastformula_events_utility.date_tab;
421
422 l_output_txt varchar2(1000);
423 l_org_info hr_organization_information.org_information1%type;
424 l_personid per_all_people_f.person_id%type;
425 l_emp_no per_all_people_f.employee_number%type;
426 l_first_name per_all_people_f.first_name%type;
427 l_last_name per_all_people_f.last_name%type;
428 l_employment_category varchar2(5);
429 l_gender per_all_people_f.sex%type;
430 l_loc_id per_all_assignments_f.location_id%type;
431 l_asg_id per_all_assignments_f.assignment_id%type;
432 l_province varchar2(5);
433 l_desg_abor per_all_people_f.per_information5%type;
434 l_desg_vminor per_all_people_f.per_information6%type;
435 l_desg_disab per_all_people_f.per_information7%type;
436 l_naics_no hr_organization_information.org_information8%type;
437 l_naics_no_gre hr_organization_information.org_information8%type;
438 l_hire_date date;
439 l_term_date date;
440 l_promo_date date;
441 l_st_date date;
442 l_start_date date;
443 l_end_dt date;
444 l_cma_code varchar2(5);
445 l_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type;
446 l_noc_code per_jobs.job_information7%type;
447 l_salary number;
448 l_flag char(1);
449 l_cnt_flag char(1);
453 l_promo_no number;
450 l_tmp_cnt number;
451 l_promo_cnt number;
452 l_excep_cnt number;
454 l_person_exists char(1);
455 l_asgchk_flag char(1);
456 Begin
457
458 --hr_utility.trace_on(null,'EQUITY');
459 v_year_start := trunc(to_date(p_year,'YYYY'),'Y');
460 v_year_end := add_months(trunc(to_date(p_year,'YYYY'),'Y'), 12) -1;
461 l_excep_cnt := 0;
462
463 /* Added by ssmukher for Bug */
464 l_asgchk_flag := 'Y';
465
466 /* Added by ssmukher in v115.1
467 Removing the records from per_ca_ee_report_lines table
468 before running any new report */
469 delete
470 from per_ca_ee_report_lines
471 where request_id in (10,20,30);
472
473 /* Caching data from per_jobs and per_person_types tables */
474
475 open cur_jobs;
476 fetch cur_jobs bulk collect into
477 v_job_id_temp,
478 v_job_noc_temp;
479
480 close cur_jobs;
481
482 if v_job_id_temp.count > 0 then
483 for i in v_job_id_temp.first..v_job_id_temp.last LOOP
484 v_job_id(v_job_id_temp(i)) := v_job_id_temp(i);
485 v_job_noc(v_job_id_temp(i)) := v_job_noc_temp(i);
486 end loop;
487 end if;
488
489 open cur_person_types;
490 fetch cur_person_types bulk collect into
491 v_person_type_temp;
492 close cur_person_types;
493
494 if v_person_type_temp.count > 0 then
495 for i in v_person_type_temp.first..v_person_type_temp.last LOOP
496 v_person_type(v_person_type_temp(i)) := v_person_type_temp(i);
497 end loop;
498 end if;
499
500
501 open cur_emp_categ_person(v_year_start);
502
503 hr_utility.trace('The value of l_asgchk_flag is '||l_asgchk_flag);
504 loop
505
506 hr_utility.trace('Inside the first loop');
507 fetch cur_emp_categ_person
508 into l_personid,
509 l_emp_no,
510 l_first_name,
511 l_last_name,
512 l_gender,
513 l_desg_abor ,
514 l_desg_vminor,
515 l_desg_disab,
516 l_hire_date;
517 hr_utility.trace('Outside the first fetch statement');
518 hr_utility.trace('Person Id :'||l_personid);
519 if cur_emp_categ_person%notfound then
520 exit;
521 end if;
522
523 /* Added code for checking the secure user */
524 open c_person_exists(l_personid);
525 fetch c_person_exists
526 into l_person_exists;
527
528 if c_person_exists%notfound then
529 close c_person_exists;
530 hr_utility.set_message(800,'PAY_74160_SUPER_USER');
531 pay_core_utils.push_message(800,'PAY_74160_SUPER_USER','P');
532 hr_utility.raise_error;
533 else
534 close c_person_exists;
535 end if;
536
537 l_term_date := null;
538 l_end_dt := null;
539 if p_naic_code is null then
540 open cur_emp_categ_asg(l_personid,v_year_start) ;
541 else
542 open cur_emp_categ_naic_asg(l_personid,v_year_start) ;
543 end if;
544
545
546 if p_naic_code is null then
547 hr_utility.trace('Inside the first loop');
548 fetch cur_emp_categ_asg
549 into l_asg_id,
550 l_noc_code,
551 l_employment_category,
552 l_loc_id,
553 l_keyflex_id,
554 l_st_date;
555
556 hr_utility.trace('Outside the first fetch statement');
557 hr_utility.trace('Assignment Id :'||l_asg_id);
558
559 else
560 fetch cur_emp_categ_naic_asg
561 into l_asg_id,
562 l_noc_code,
563 l_employment_category,
564 l_loc_id,
565 l_st_date;
566
567 end if;
568 /* Added by ssmukher for Bug 4493278 */
569 if p_naic_code is null then
570 if cur_emp_categ_asg%notfound then
571 l_asgchk_flag := 'N';
572 else
573 l_asgchk_flag := 'Y';
574 end if;
575 close cur_emp_categ_asg ;
576 else
577 hr_utility.trace('Code is null');
578 if cur_emp_categ_naic_asg%notfound then
579 hr_utility.trace('No record found for the person in cur_emp_categ_naic_asg cursor');
580 l_asgchk_flag := 'N';
581 else
582 l_asgchk_flag := 'Y';
583 end if;
584 close cur_emp_categ_naic_asg ;
585 end if;
586
587 /* Added by ssmukher for Bug 4493278 checking if the assignment corresponding to the
588 person is fetched or not */
589 hr_utility.trace('The value of l_asgchk_flag after all check '||l_asgchk_flag);
590 if l_asgchk_flag = 'Y' then
591
592 l_flag :='Y';
593 l_cnt_flag := 'N';
594
595 hr_utility.trace('The hire date is '||to_char(l_hire_date,'YYYY-MM-DD'));
596 open cur_emp_prov(l_loc_id);
597 fetch cur_emp_prov
598 into l_province;
599 if cur_emp_prov%notfound then
600 l_province := '-99';
601 end if;
602 hr_utility.trace('the province value is '||l_province||'for location id'||l_loc_id||'Naics code '||l_naics_no);
603 close cur_emp_prov;
604
605 open cur_emp_cma(l_loc_id);
606 fetch cur_emp_cma
607 into l_cma_code;
608 if cur_emp_cma%notfound then
609 l_cma_code := '-99';
610 end if;
611 close cur_emp_cma;
612
613 if p_naic_code is null then
614 open cur_naic_code(l_keyflex_id);
618 l_naics_no := '-999';
615 fetch cur_naic_code
616 into l_naics_no;
617 if cur_naic_code%notfound then
619 else
620 l_naics_no := lpad(l_naics_no,4,'0');
621 end if;
622 close cur_naic_code;
623
624 hr_utility.trace('The NAIC code for GRE is '||l_naics_no);
625 else
626 /* Added by ssmukher for Bug 4501549 */
627 open cur_naic_code(l_keyflex_id);
628 fetch cur_naic_code
629 into l_naics_no;
630 if cur_naic_code%found then
631 l_naics_no := lpad(l_naics_no,4,'0');
632 else
633 l_naics_no := lpad(p_naic_code,4,'0');
634 end if;
635 close cur_naic_code;
636 end if;
637
638 /* Code for reporting the employees in Exception file for Missing Information
639 into the table */
640
641 if l_province = -99 then
642 l_flag :='N';
643 end if;
644
645 if l_naics_no = '-999' then
646 l_flag :='N';
647 end if;
648
649 if l_cma_code = '-99' then
650 l_flag :='N';
651 end if;
652
653 if l_employment_category = '-1' then
654 l_flag := 'N';
655 end if;
656
657
658 open c_total_salary(l_personid);
659 fetch c_total_salary
660 into l_salary;
661 if c_total_salary%notfound then
662 l_salary := 0;
663 end if;
664 close c_total_salary;
665
666 if l_salary = 0 then
667 l_flag := 'N';
668 end if;
669
670 /* Storing the employee records with incomplete information for generating
671 the exception report */
672
673 if l_flag = 'N' then
674 l_excep_cnt := l_excep_cnt + 1;
675 insert into
676 per_ca_ee_report_lines (REQUEST_ID ,
677 LINE_NUMBER,
678 CONTEXT,
679 SEGMENT1,
680 SEGMENT2,
681 SEGMENT3,
682 SEGMENT4,
683 SEGMENT5,
684 SEGMENT6)
685 select 30,
686 l_excep_cnt,
687 'EXCEP',
688 l_emp_no,
689 decode(l_province,'-99','Province Code'),
690 decode(l_naics_no ,'-999','NAIC Code'),
691 decode (l_cma_code,'-99','CMA Code'),
692 decode (l_employment_category,'-1','Employment Category'),
693 decode(l_salary,0,'Salary')
694 from dual;
695
696 end if;
697
698 if l_employment_category in ('01','02') then
699 begin
700 open cur_term_date( l_personid,v_year_start,v_year_end);
701 fetch cur_term_date
702 into l_term_date,
703 l_end_dt;
704 if cur_term_date%found then
705 l_cnt_flag := 'Y';
706 end if;
707 close cur_term_date;
708 exception
709 when others then
710 l_term_date := null;
711 l_end_dt := null;
712 close cur_term_date;
713 end ;
714 end if;
715
716 /* Inserting the Temporary Employee record into the table */
717 /* Checking firstly if the employee details are correct */
718 /* Added by ssmukher in v115.1 */
719 if l_flag = 'Y' then
720 if l_employment_category = '03' then
721 l_tmp_cnt := 0;
722 open cur_temp_term_date( l_personid,v_year_start,v_year_end);
723 loop
724
725 fetch cur_temp_term_date
726 into l_start_date,
727 l_term_date,
728 l_end_dt;
729 if cur_temp_term_date%found then
730 l_cnt_flag := 'Y';
731 else
732 exit;
733 end if;
734
735 if l_cnt_flag = 'Y' then
736 l_tmp_cnt := l_tmp_cnt + 1;
737 end if;
738 insert into
739 per_ca_ee_report_lines (REQUEST_ID ,
740 LINE_NUMBER,
741 CONTEXT,
742 SEGMENT1,
743 SEGMENT2,
744 SEGMENT3,
745 SEGMENT4)
746 values (10,
747 l_tmp_cnt,
748 'TMP',
749 l_emp_no,
750 to_char(l_start_date,'YYYY/MM/DD'),
751 to_char(l_end_dt,'YYYY/MM/DD'),
752 to_char(l_term_date,'YYYY/MM/DD'));
753 end loop;
754 close cur_temp_term_date;
755 end if;
756 end if;
757 if l_flag = 'Y' then
758 if l_employment_category in ('01','02') then
759 l_output_txt := l_emp_no|| fnd_global.local_chr(9)||l_cma_code||fnd_global.local_chr(9)||l_province ||fnd_global.local_chr(9)
760 ||l_noc_code||fnd_global.local_chr(9)||l_naics_no||fnd_global.local_chr(9)||l_employment_category
761 ||fnd_global.local_chr(9)||fnd_global.local_chr(9)||l_gender||fnd_global.local_chr(9)||l_last_name
762 ||fnd_global.local_chr(9)||l_first_name||fnd_global.local_chr(9)||l_salary||fnd_global.local_chr(9)
763 ||l_desg_abor||fnd_global.local_chr(9)||l_desg_vminor||fnd_global.local_chr(9)||l_desg_disab
764 ||fnd_global.local_chr(9)||to_char(l_hire_date,'YYYY/MM/DD')||fnd_global.local_chr(9)
768
765 ||to_char(l_term_date,'YYYY/MM/DD')||fnd_global.local_chr(9)||fnd_global.local_chr(9)
766 ||fnd_global.local_chr(9)||fnd_global.local_chr(9);
767 else
769 if l_tmp_cnt > 0 then
770 l_output_txt := l_emp_no|| fnd_global.local_chr(9)||l_cma_code||fnd_global.local_chr(9)||l_province
771 ||fnd_global.local_chr(9)||l_noc_code||fnd_global.local_chr(9)||l_naics_no||fnd_global.local_chr(9)
772 ||l_employment_category||fnd_global.local_chr(9)||fnd_global.local_chr(9)
773 ||l_gender||fnd_global.local_chr(9)||l_last_name || fnd_global.local_chr(9)||l_first_name
774 ||fnd_global.local_chr(9)||l_salary||fnd_global.local_chr(9)||l_desg_abor||fnd_global.local_chr(9)||l_desg_vminor
775 ||fnd_global.local_chr(9)||l_desg_disab||fnd_global.local_chr(9)||to_char(l_hire_date,'YYYY/MM/DD')
776 ||fnd_global.local_chr(9)||fnd_global.local_chr(9)||fnd_global.local_chr(9)||fnd_global.local_chr(9)
777 ||fnd_global.local_chr(9)||fnd_global.local_chr(9)||to_char(l_tmp_cnt,'09');
778 else
779 l_output_txt := l_emp_no|| fnd_global.local_chr(9)||l_cma_code||fnd_global.local_chr(9)||l_province
780 ||fnd_global.local_chr(9)||l_noc_code||fnd_global.local_chr(9)||l_naics_no
781 ||fnd_global.local_chr(9)||l_employment_category||fnd_global.local_chr(9)||fnd_global.local_chr(9)
782 ||l_gender||fnd_global.local_chr(9)||l_last_name || fnd_global.local_chr(9)||l_first_name
783 ||fnd_global.local_chr(9)||l_salary||fnd_global.local_chr(9)||l_desg_abor||fnd_global.local_chr(9)||l_desg_vminor
784 ||fnd_global.local_chr(9)||l_desg_disab||fnd_global.local_chr(9)||to_char(l_hire_date,'YYYY/MM/DD')
785 ||fnd_global.local_chr(9)||fnd_global.local_chr(9)||fnd_global.local_chr(9)||fnd_global.local_chr(9)
786 ||fnd_global.local_chr(9)||fnd_global.local_chr(9);
787 end if;
788 end if;
789
790 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_output_txt);
791 end if;
792
793
794 /* Inserting the Promotion details in the table */
795 begin
796 l_promo_cnt := 0;
797 hr_utility.trace('The value of the person id is '||l_personid);
798 /* Added by ssmukher in v115.1 */
799 /* Checking firstly if the employee details are correct */
800 if l_flag = 'Y' then
801
802 open c_promo(l_personid,
803 v_year_start,
804 v_year_end);
805
806 loop
807
808 fetch c_promo
809 into l_asg_id,
810 l_promo_date ;
811
812 if c_promo%notfound then
813 exit;
814 end if;
815 l_promo_cnt := l_promo_cnt + 1;
816
817 insert into
818 per_ca_ee_report_lines (REQUEST_ID ,
819 LINE_NUMBER,
820 CONTEXT,
821 SEGMENT1,
822 SEGMENT2)
823 values (20,
824 l_promo_cnt,
825 'PROMO',
826 l_emp_no,
827 to_char(l_promo_date,'YYYY/MM/DD'));
828
829 end loop;
830 close c_promo;
831 hr_utility.trace('The Person id is '||l_personid);
832 l_promo_no := PER_FASTFORMULA_EVENTS_UTILITY.per_fastformula_event('PROMOTION',
833 'Promotion',
834 p_business_group_id,
835 l_personid,
836 v_year_start,
837 v_year_end,
838 table_date);
839 end if;
840
841 hr_utility.trace('The value of promotion number is '||l_promo_no);
842 hr_utility.trace('The first record in the plsql table is '||nvl(table_date.count,0));
843 hr_utility.trace('The person_id been checked is '||l_personid);
844
845 if l_flag = 'Y' then
846 if l_promo_no <> 0 then
847
848 for i in nvl(table_date.first,0)..nvl(table_date.last,-1)
849 loop
850 hr_utility.trace('Inside the PLSQl table ');
851 hr_utility.trace('The value of the date fetched is '||table_date(i));
852 l_promo_cnt := l_promo_cnt + 1;
853 insert into
854 per_ca_ee_report_lines (REQUEST_ID ,
855 LINE_NUMBER,
856 CONTEXT,
857 SEGMENT1,
858 SEGMENT2)
859 values (20,
860 l_promo_cnt,
861 'PROMO',
862 l_emp_no,
863 to_char(table_date(i),'YYYY/MM/DD'));
864 end loop;
865 end if;
866 end if;
867 end;
868 end if; /* Added by ssmukher for Bug No 4493278 */
869 end loop;
870
871 hr_utility.trace('Outside the cur_emp_categ cursor ');
872 close cur_emp_categ_person;
873
874
875 COMMIT;
876 End;
877
878
879 /**************** End of Procedure ******************************/
880
881 /************* Start of procedure emp_promotion *******************/
882 procedure emp_promotions (errbuf out nocopy varchar2,
883 retcode out nocopy number,
884 p_business_group_id in number,
888 p_end_date in date ) is
885 p_year in varchar2,
886 p_naic_code in varchar2,
887 p_start_date in date,
889
890 cursor c_promo_details is
891 select to_number(segment1) emp_no,
892 segment2 promo_dt
893 from per_ca_ee_report_lines pcer
894 where pcer.context = 'PROMO' and
895 pcer.request_id = 20
896 order by emp_no,promo_dt;
897
898 l_emp_no per_all_people_f.employee_number%type;
899 l_prev_emp per_all_people_f.employee_number%type;
900 l_promo_dt varchar2(10);
901 l_promo_cnt number;
902 l_output varchar2(1000);
903 begin
904 l_prev_emp := -999;
905 open c_promo_details;
906 loop
907 fetch c_promo_details
908 into l_emp_no,
909 l_promo_dt;
910 if c_promo_details%notfound then
911 exit;
912 end if;
913 if l_prev_emp <> l_emp_no then
914 l_promo_cnt := 1;
915 else
916 l_promo_cnt := l_promo_cnt + 1;
917 end if;
918 l_output := l_emp_no ||fnd_global.local_chr(9)||l_promo_cnt || fnd_global.local_chr(9) || l_promo_dt;
919 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_output);
920 l_prev_emp := l_emp_no;
921 end loop;
922 /* Deleteing the promotion records from per_ca_ee_report_lines table */
923 delete from
924 per_ca_ee_report_lines
925 where request_id = 20 and context = 'PROMO';
926 commit;
927 end ;
928 /*************** End of Procedure to print the Promotion details list *****/
929
930 /*************** Start of Procedure to print the terminated employee list *****/
931 procedure count_term(errbuf out nocopy varchar2,
932 retcode out nocopy number,
933 p_business_group_id in number,
934 p_year in varchar2,
935 p_naic_code in varchar2) is
936
937 cursor c_term_details is
938 select to_number(segment1) emp_no,
939 segment2 start_dt,
940 segment3 end_dt,
941 segment4 term_dt
942 from per_ca_ee_report_lines pcer
943 where pcer.context = 'TMP' and
944 pcer.request_id = 10
945 order by emp_no;
946
947 l_emp_no per_all_people_f.employee_number%type;
948 l_term_cnt number;
949 l_start_dt varchar2(10);
950 l_end_dt varchar2(10);
951 l_term_dt varchar2(10);
952 l_output varchar2(1000);
953 l_prev_emp per_all_people_f.employee_number%type;
954 BEGIN
955 l_prev_emp := -999;
956 open c_term_details;
957 loop
958 fetch c_term_details
959 into l_emp_no ,
960 l_start_dt,
961 l_end_dt ,
962 l_term_dt;
963 if c_term_details%notfound then
964 exit;
965 end if;
966
967 if l_term_dt is not null then
968 if l_emp_no <> l_prev_emp then
969 l_term_cnt := 1;
970 else
971 l_term_cnt := l_term_cnt + 1;
972 end if;
973 else
974
975 l_term_cnt := 0;
976 end if;
977 if l_term_cnt > 0 then
978 if l_end_dt <> l_term_dt then
979 l_output := l_emp_no ||fnd_global.local_chr(9)||l_term_cnt||fnd_global.local_chr(9)||l_start_dt
980 ||fnd_global.local_chr(9)||l_end_dt||fnd_global.local_chr(9)||l_term_dt;
981 else
982 l_output := l_emp_no ||fnd_global.local_chr(9)||l_term_cnt||fnd_global.local_chr(9)
983 ||l_start_dt||fnd_global.local_chr(9)||l_end_dt;
984 end if;
985 else
986 l_output := l_emp_no ||fnd_global.local_chr(9)||fnd_global.local_chr(9)||l_start_dt||fnd_global.local_chr(9)
987 ||l_end_dt||fnd_global.local_chr(9)||l_term_dt;
988 end if;
989 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_output);
990 l_prev_emp := l_emp_no;
991 end loop;
992 /* Deleting the temporary employee records from the per_ca_ee_report_lines table */
993 delete from
994 per_ca_ee_report_lines
995 where request_id = 10 and context ='TMP';
996 commit;
997 END ;
998
999
1000 /*************** End of Procedure to print the terminated employee list *****/
1001
1002 /*************** Procedure to print the exception report ******************/
1003
1004 procedure excep_report(errbuf out nocopy varchar2,
1005 retcode out nocopy number,
1006 p_business_group_id in number,
1007 p_year in varchar2,
1008 p_naic_code in varchar2) is
1009
1010 cursor c_excep_report is
1011 select to_number(segment1) emp_no,
1012 segment2 prov,
1013 segment3 naic,
1014 segment4 cma,
1015 segment5 emp_catg,
1016 segment6 sal
1017 from per_ca_ee_report_lines pcer
1018 where pcer.request_id = 30 and
1019 pcer.context = 'EXCEP'
1020 order by emp_no;
1021 l_emp_no per_all_people_f.employee_number%type;
1022 l_prov varchar2(20);
1023 l_naic varchar2(20);
1024 l_cma varchar2(20);
1025 l_emp_catg varchar2(20);
1026 l_sal varchar2(10);
1027 l_flag char(1);
1028 l_output varchar2(1000);
1029 l_count number;
1030 begin
1031 -- hr_utility.trace_on(null,'Exception');
1032 l_flag := 'N';
1033 open c_excep_report;
1034 loop
1035 fetch c_excep_report
1036 into l_emp_no,
1037 l_prov,
1038 l_naic,
1039 l_cma,
1043 exit;
1040 l_emp_catg,
1041 l_sal;
1042 if c_excep_report%notfound then
1044 end if;
1045 l_count := 0;
1046 hr_utility.trace('The value of Employee number is '||l_emp_no);
1047 l_output := 'Following information are missing for Employee :'||l_emp_no;
1048 if l_prov is not null then
1049 l_count := l_count + 1;
1050 l_output := l_output ||fnd_global.local_chr(9)||l_count||')'|| l_prov;
1051 l_flag := 'Y';
1052 end if;
1053
1054 if l_naic is not null then
1055 l_count := l_count + 1;
1056 l_output := l_output ||fnd_global.local_chr(9)||l_count||')'|| l_naic;
1057 l_flag := 'Y';
1058 end if;
1059
1060 if l_cma is not null then
1061 l_count := l_count + 1;
1062 l_output := l_output ||fnd_global.local_chr(9)||l_count||')'|| l_cma;
1063 l_flag :='Y';
1064 end if;
1065
1066 if l_emp_catg is not null then
1067 l_count := l_count + 1;
1068 l_output := l_output || fnd_global.local_chr(9)||l_count||')'||l_emp_catg;
1069 end if;
1070
1071 if l_sal is not null then
1072 l_count := l_count + 1;
1073 l_output := l_output ||fnd_global.local_chr(9)||l_count||')'|| l_sal;
1074 end if;
1075
1076 if l_flag = 'Y' then
1077 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_output);
1078 end if;
1079 end loop;
1080 close c_excep_report;
1081 /* Delete the Exception employee records from the Temporary tables */
1082 delete from
1083 per_ca_ee_report_lines
1084 where request_id = 30 and
1085 context = 'EXCEP';
1086 commit;
1087 end;
1088
1089
1090 /* End Of package */
1091 End;
[Home] [Help]