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