[Home] [Help]
PACKAGE BODY: APPS.PER_ZA_EMPLOYMENT_EQUITY_PKG
Source
1 PACKAGE BODY PER_ZA_EMPLOYMENT_EQUITY_PKG as
2 /* $Header: perzaeer.pkb 120.30.12010000.7 2008/09/01 09:06:03 rbabla ship $ */
3 /*
4 ==============================================================================
5 This package loads data into table per_za_employment_equity for use by
6 the Employment Equity Reports
7
8 MODIFICATION HISTORY
9
10 Name Date Version Bug Text
11 -------------- ----------- ------- ------- -----------------------------
12 R. Kingham 22 May 2000 110.0 Initial Version
13 R. Kingham 15 Jun 2000 110.1 Added Extra functionality for EEQ.
14 D. Son 20 Jun 2001 110.2 Removed data load dbms_sql procedures.
15 Replaced with lookup functions and
16 insert procedures.
17 F.D. Loubser 11 Sep 2001 115.4 Almost complete rewrite for 11i.
18 F.D. Loubser 10 Dec 2001 115.7 Business_group_id on user table
19 F.D. Loubser 1 Feb 2002 115.8 QA fixes
20 F.D. Loubser 1 Feb 2002 115.9 Added checkfile
21 F.D. Loubser 7 Feb 2002 115.10 Removed low list skip optimization
22 F.D. Loubser 14 Feb 2002 115.11 Added multiple legal entity
23 J.N. Louw 5 Apr 2002 115.12 Bug 2306877
24 Fixed default select statements
25 each section EQ1 to 6
26 F.D. Loubser 9 May 2002 115.13 g_cat_flex variable too small
27 Nirupa S 05 Dec 2002 115.14 2686695 In accordance with the APPS-wide
28 performance changes for 11.5.9,
29 NOCOPY has been added to all
30 OUT and IN OUT parameters.
31 Nirupa S 10 Dec 2002 115.14 2686695 Added exception handling for
32 NOCOPY in calc_highest_and_lowest_avg
33 A.Sengar 11 Dec 2002 115.16 2665394 Modified the query to improve
34 performance.
35 Nageswara 17 Nov 2004 115.17 3962073 modified query to fetch Employee Type
36 when user has entered value.
37 Supressed GSCC warnings
38 Nageswara 24 Nov 2004 115.18 4027769 modified select query to all disabilities
39 'F','P' as 'Y' (Disabled)
40 Kaladhaur P 28-Jun-2005 115.20 4445926 Fix GSCC Error: File.Sql.8 and File.Sql.18
41 Kaladhaur P 28-Jun-2005 115.21 4413678 Function get_avg_5_lowest_salary has been
42 modified to process employees with race
43 not equal to 'Not Used'.
44 A. Mahanty 19-Dec-2005 115.24 4872110 R12 Performance Bug fix. Modified the query in
45 function get_avg_5_lowest_salary
46 A. Mahanty 21-Dec-2005 115.25 4872110 R12 Performance Bug fix. Modified the queries in
47 function populate_ee_table
48 Kaladhaur P 22-May-2006 115.26 4413678 Function get_avg_5_lowest_salary has been
49 modified to process employees with race
50 not equal to 'Not Used'.
51 Kaladhaur P 20-Jun-2006 115.27 5228238 Query in function get_lookup_code has been
52 modified to fetch one row for lookup meaning
53 'Not Applicable'.
54 Kaladhaur P 04-Jul-2006 115.28 4413678 Modified the comments.
55 R Pahune 24-Jul-2006 115.30 5406242 Employment equity enhancment.
56 R Pahune 08-jan-2008 115.43 6773326 To get correct Employment Category due to
57 changes for WSP.
58 R Babla 18-Feb-2008 115.49 6817148 Changes in populate_ee_table to consider the
59 new segment added for foreign national
60 R Babla 23-Jul-2008 115.50 7277745/Changes done to consider chinese as Africans
61 7237663 and to cater for employer contribution
62 R Babla 30-Jul-2008 115.51 7277745/Changes done to add 'Normal' to the balance name
63 7237663 for Normal ER contribution
64 P Arusia 25-Aug-2008 115.52 7277745 Corrected the procedure
65 init_g_cat_lev_table to add l_er_annual_income
66 to net annual income
67 R Babla 30-Jul-2008 115.54 7360563 Changes done in cursor c_assignments of
68 proc init_g_cat_lev_table so as not to
69 include employee in differential report
70 which has any of occupational cat/ level
71 or function type as 'Not Applicable'
72 ==============================================================================
73 */
74
75 -- Global types
76 type r_assignments is record
77 (
78 payroll_id per_all_assignments_f.payroll_id%type,
79 legal_entity_id hr_all_organization_units.organization_id%type,
80 legal_entity hr_all_organization_units.name%type,
81 occupational_level_id hr_lookups.lookup_code%type,
82 occupational_category_id hr_lookups.lookup_code%type,
83 occupational_level hr_lookups.meaning%type,
84 occupational_category hr_lookups.meaning%type,
85 race per_all_people_f.per_information4%type,
86 sex per_all_people_f.sex%type,
87 annual_income number
88 );
89
90 type r_averages is record
91 (
92 high number,
93 low number
94 );
95
96 -- Added for Employment equity report enhancement
97 type r_Encome_diff_rec is record
98 (
99 legal_entity_id hr_all_organization_units.organization_id%type,
100 legal_entity hr_all_organization_units.NAME%type,
101 occupational_code_id hr_lookups.lookup_code%type,
102 occupational_code hr_lookups.meaning%type,
103 ma number,
104 mc number,
105 mi number,
106 mw number,
107 fa number,
108 fc number,
109 fi number,
110 fw number,
111 total number,
112 ma_inc number,
113 mc_inc number,
114 mi_inc number,
115 mw_inc number,
116 fa_inc number,
117 fc_inc number,
118 fi_inc number,
119 fw_inc number,
120 total_inc number
121 );
122
123 type t_assignments is table of r_assignments index by binary_integer;
124 type t_averages is table of r_averages index by binary_integer;
125
126 TYPE t_E_differential IS TABLE OF r_Encome_diff_rec INDEX BY binary_integer;
127
128 -- Global variables
129 g_package constant varchar2(30) := 'per_za_employment_equity_pkg.';
130 g_assignments_table t_assignments;
131 g_cat_averages_table t_averages;
132 g_lev_averages_table t_averages;
133
134 g_cat_Enc_Diff_table t_E_differential;
135 g_lev_Enc_Diff_table t_E_differential;
136
137 g_grade_name per_grades.name%type;
138 g_grade_report_date date;
139 g_grade_asg_id per_all_assignments_f.assignment_id%type;
140
141 g_position_name per_all_positions.name%type;
142 g_pos_report_date date;
143 g_pos_asg_id per_all_assignments_f.assignment_id%type;
144
145 g_job_name per_jobs.name%type;
146 g_job_report_date date;
147 g_job_asg_id per_all_assignments_f.assignment_id%type;
148
149 g_lev_name hr_lookups.meaning%type;
150 g_lev_report_date date;
151 g_lev_asg_id per_all_assignments_f.assignment_id%type;
152
153 g_cat_name hr_lookups.meaning%type;
154 g_cat_report_date date;
155 g_cat_asg_id per_all_assignments_f.assignment_id%type;
156
157 g_lev_flex pay_user_column_instances_f.value%type := null;
158 g_lev_segment pay_user_column_instances_f.value%type := null;
159 g_cat_flex pay_user_column_instances_f.value%type := null;
160 g_cat_segment pay_user_column_instances_f.value%type := null;
161 g_f_type_name hr_lookups.meaning%type;
162 g_Func_flex pay_user_column_instances_f.value%type := null;
163 g_Func_segment pay_user_column_instances_f.value%type := null;
164
165 g_high1 number := 0;
166 g_high2 number := 0;
167 g_high3 number := 0;
168 g_high4 number := 0;
169 g_high5 number := 0;
170
171 g_low1 number := 999999999999999;
172 g_low2 number := 999999999999999;
173 g_low3 number := 999999999999999;
174 g_low4 number := 999999999999999;
175 g_low5 number := 999999999999999;
176
177 g_all_high_avg number := -9999;
178 g_all_low_avg number := -9999;
179
180 -- This procedure resets the list of highest and lowest values.
181 procedure reset_high_low_lists is
182
183 l_proc constant varchar2(60) := g_package || 'reset_high_low_lists';
184
185 begin
186
187 hr_utility.set_location('Entering ' || l_proc, 10);
188
189 g_high1 := 0;
190 g_high2 := 0;
191 g_high3 := 0;
192 g_high4 := 0;
193 g_high5 := 0;
194
195 g_low1 := 999999999999999;
196 g_low2 := 999999999999999;
197 g_low3 := 999999999999999;
198 g_low4 := 999999999999999;
199 g_low5 := 999999999999999;
200
201 end reset_high_low_lists;
202
203 -- This procedure returns the average of the 5 highes and lowest values from the lists.
204 procedure calc_highest_and_lowest_avg
205 (
206 p_high_avg out nocopy number,
207 p_low_avg out nocopy number
208 ) is
209
210 l_high_avg number := 0;
211 l_low_avg number := 0;
212 l_count integer := 0;
213
214 begin
215
216 if g_high1 <> 0 then
217 l_count := 1;
218 l_high_avg := g_high1;
219 end if;
220 if g_high2 <> 0 then
221 l_count := l_count + 1;
222 l_high_avg := l_high_avg + g_high2;
223 end if;
224 if g_high3 <> 0 then
225 l_count := l_count + 1;
226 l_high_avg := l_high_avg + g_high3;
227 end if;
228 if g_high4 <> 0 then
229 l_count := l_count + 1;
230 l_high_avg := l_high_avg + g_high4;
231 end if;
232 if g_high5 <> 0 then
233 l_count := l_count + 1;
234 l_high_avg := l_high_avg + g_high5;
235 end if;
236 if l_count = 0 then
237 p_high_avg := 0;
238 else
239 p_high_avg := l_high_avg / l_count;
240 end if;
241
242 l_count := 0;
243 if g_low1 <> 999999999999999 then
244 l_count := 1;
245 l_low_avg := g_low1;
246 end if;
247 if g_low2 <> 999999999999999 then
248 l_count := l_count + 1;
249 l_low_avg := l_low_avg + g_low2;
250 end if;
251 if g_low3 <> 999999999999999 then
252 l_count := l_count + 1;
253 l_low_avg := l_low_avg + g_low3;
254 end if;
255 if g_low4 <> 999999999999999 then
256 l_count := l_count + 1;
257 l_low_avg := l_low_avg + g_low4;
258 end if;
259 if g_low5 <> 999999999999999 then
260 l_count := l_count + 1;
261 l_low_avg := l_low_avg + g_low5;
262 end if;
263 if l_count = 0 then
264 p_low_avg := 0;
265 else
266 p_low_avg := l_low_avg / l_count;
267 end if;
268 --
269 exception
270 when others then
271 p_high_avg := null;
272 p_low_avg := null;
273 --
274
275 end calc_highest_and_lowest_avg;
276
277 -- This procedure maintains a list of the 5 highest and lowest values passed to it.
278 procedure get_highest_and_lowest(p_value in number) is
279
280 l_proc constant varchar2(60) := g_package || 'get_highest_and_lowest';
281
282 begin
283
284 hr_utility.set_location('Entering ' || l_proc, 10);
285 hr_utility.set_location('p_value ' || to_char(p_value), 20);
286
287 -- Ignore the value if it is zero
288 if p_value <> 0 then
289
290 -- Determine whether the value belongs in the highest list
291 if p_value > g_high5 then
292
293 if p_Value > g_high4 then
294
295 if p_value > g_high3 then
296
297 if p_value > g_high2 then
298
299 if p_value > g_high1 then
300
301 g_high5 := g_high4;
302 g_high4 := g_high3;
303 g_high3 := g_high2;
304 g_high2 := g_high1;
305 g_high1 := p_value;
306
307 else
308
309 g_high5 := g_high4;
310 g_high4 := g_high3;
311 g_high3 := g_high2;
312 g_high2 := p_value;
313
314 end if;
315
316 else
317
318 g_high5 := g_high4;
319 g_high4 := g_high3;
320 g_high3 := p_value;
321
322 end if;
323
324 else
325
326 g_high5 := g_high4;
327 g_high4 := p_value;
328
329 end if;
330
331 else
332
333 g_high5 := p_value;
334
335 end if;
336
337 end if;
338
339 hr_utility.set_location('g_high1 ' || to_char(g_high1), 40);
340 hr_utility.set_location('g_high2 ' || to_char(g_high2), 50);
341 hr_utility.set_location('g_high3 ' || to_char(g_high3), 60);
342 hr_utility.set_location('g_high4 ' || to_char(g_high4), 70);
343 hr_utility.set_location('g_high5 ' || to_char(g_high5), 80);
344
345 -- Determine whether the value belongs in the lowest list
346 if p_value < g_low5 then
347
348 if p_value < g_low4 then
349
350 if p_value < g_low3 then
351
352 if p_value < g_low2 then
353
354 if p_value < g_low1 then
355
356 g_low5 := g_low4;
357 g_low4 := g_low3;
358 g_low3 := g_low2;
359 g_low2 := g_low1;
360 g_low1 := p_value;
361
362 else
363
364 g_low5 := g_low4;
365 g_low4 := g_low3;
366 g_low3 := g_low2;
367 g_low2 := p_value;
368
369 end if;
370
371 else
372
373 g_low5 := g_low4;
374 g_low4 := g_low3;
375 g_low3 := p_value;
376
377 end if;
378
379 else
380
381 g_low5 := g_low4;
382 g_low4 := p_value;
383
384 end if;
385
386 else
387
388 g_low5 := p_value;
389
390 end if;
391
392 end if;
393
394 hr_utility.set_location('g_low1 ' || to_char(g_low1), 90);
395 hr_utility.set_location('g_low2 ' || to_char(g_low2), 100);
396 hr_utility.set_location('g_low3 ' || to_char(g_low3), 110);
397 hr_utility.set_location('g_low4 ' || to_char(g_low4), 120);
398 hr_utility.set_location('g_low5 ' || to_char(g_low5), 130);
399
400 end if; -- Zero check
401
402 end get_highest_and_lowest;
403
404 -- This function returns the number of days the assignment's status was Active Assignment
405 -- Note: Suspended Assignment is not seen as active in this case, since it is not
406 -- income generating
407 function get_active_days
408 (
409 p_assignment_id number,
410 p_report_start date,
411 p_report_end date
412 ) return number is
413
414 l_count number;
415
416 begin
417
418 select sum
419 (
420 decode(sign(p_report_end - paaf.effective_end_date), 1, paaf.effective_end_date, p_report_end)
421 -
422 decode(sign(p_report_start - paaf.effective_start_date), 1, p_report_start, paaf.effective_start_date)
423 + 1
424 )
425 into l_count
426 from per_assignment_status_types past,
427 per_all_assignments_f paaf
428 where paaf.assignment_id = p_assignment_id
429 and past.assignment_status_type_id = paaf.assignment_status_type_id
430 and past.per_system_status = 'ACTIVE_ASSIGN'
431 and paaf.effective_start_date <= p_report_end
432 and paaf.effective_end_date >= p_report_start;
433
434 return l_count;
435
436 exception
437 when no_data_found then
438 return 0;
439
440 end get_active_days;
441
442 -- This function returns the termination reason from the user tables.
443 function get_termination_reason
444 (
445 p_business_group_id in per_all_assignments_f.business_group_id%type,
446 p_report_date in per_all_assignments_f.effective_end_date%type,
447 p_reason_code in per_periods_of_service.leaving_reason%type
448 ) return varchar2 is
449
450 l_termination_reason pay_user_column_instances_f.value%type;
451
452 begin
453
454 select pucifcat.value
455 into l_termination_reason
456 from pay_user_column_instances_f pucifcat,
457 pay_user_rows_f purfcat,
458 pay_user_columns puccat,
459 pay_user_rows_f purfqc,
460 pay_user_column_instances_f pucifqc,
461 pay_user_columns pucqc,
462 pay_user_tables put
463 where put.user_table_name = 'ZA_TERMINATION_CATEGORIES'
464 and put.business_group_id is null
465 and put.legislation_code = 'ZA'
466 and pucqc.user_table_id = put.user_table_id
467 and pucqc.user_column_name = 'Lookup Code'
468 and pucifqc.user_column_id = pucqc.user_column_id
469 and pucifqc.business_group_id = p_business_group_id
470 and p_report_date between pucifqc.effective_start_date and pucifqc.effective_end_date
471 and pucifqc.value = p_reason_code
472 and purfqc.user_table_id = put.user_table_id
473 and purfqc.user_row_id = pucifqc.user_row_id
474 and purfqc.business_group_id = pucifqc.business_group_id
475 and p_report_date between purfqc.effective_start_date and purfqc.effective_end_date
476 and puccat.user_table_id = put.user_table_id
477 and puccat.user_column_name = 'Termination Category'
478 and purfcat.user_table_id = put.user_table_id
479 and purfcat.business_group_id = pucifqc.business_group_id
480 and p_report_date between purfcat.effective_start_date and purfcat.effective_end_date
481 and purfcat.row_low_range_or_name = purfqc.row_low_range_or_name
482 and pucifcat.user_column_id = puccat.user_column_id
483 and pucifcat.user_row_id = purfcat.user_row_id
484 and p_report_date between pucifcat.effective_start_date and pucifcat.effective_end_date
485 and pucifcat.business_group_id = pucifqc.business_group_id
486 and pucifcat.value in
487 (
488 'Resignation',
489 'Non-Renewal of Contract',
490 'Dismissal - Operational Requirements',
491 'Dismissal - Misconduct',
492 'Dismissal - Incapacity',
493 'Other'
494 );
495
496 return l_termination_reason;
497
498 exception
499 when no_data_found then
500 return 'No Leaving Reason';
501
502 end get_termination_reason;
503
504 -- This procedure resets all the data structures for the Income Differentials report.
505 procedure reset_tables is
506
507 l_proc constant varchar2(60) := g_package || 'reset_tables';
508
509 begin
510
511 -- hr_utility.trace_on(null, 'T');
512 hr_utility.set_location('Entering ' || l_proc, 10);
513
514 g_assignments_table.delete;
515 g_all_high_avg := -9999;
516 g_all_low_avg := -9999;
517 g_cat_averages_table.delete;
518 g_lev_averages_table.delete;
519 g_cat_Enc_Diff_table.delete;
520 g_lev_Enc_Diff_table.delete;
521 DELETE FROM per_za_employment_equity
522 Where report_id IN
523 ( 'ED1', 'ED2', 'ED1I','ED2I');
524
525 end reset_tables;
526
527 -- This function returns the average 5 highest paid employees per category or level.
528 function get_avg_5_highest_salary
529 (
530 p_report_date in per_all_assignments_f.effective_end_date%type,
531 p_business_group_id in per_all_assignments_f.business_group_id%type,
532 p_legent_param in per_assignment_extra_info.aei_information7%type := null,
533 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
534 p_occupational_level_cat in hr_lookups.meaning%type,
535 p_lookup_code in hr_lookups.lookup_code%type,
536 p_occupational_type in varchar2, -- CAT = Category, LEV = Level
537 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
538 ) return number is
539
540 l_avg_5_highest_salary number;
541 l_index number;
542 l_proc constant varchar2(60) := g_package || 'get_avg_5_highest_salary';
543
544 begin
545
546 hr_utility.set_location('Entering ' || l_proc, 10);
547
548 -- The index is calculted by multiplying the legal entity id by 100 and then adding the lookup code
549 -- This should always give a unique value, since the lookup code is less than 100
550 begin
551
552 l_index := p_legal_entity_id * 100 + p_lookup_code;
553 hr_utility.set_location('LEV/CAT INDEX ' || l_index, 25);
554
555 exception
556 when others then
557 raise_application_error(-20006, 'The lookup code in the ZA_EMP_EQ_OCCUPATIONAL_LEV and ZA_EMP_EQ_OCCUPATIONAL_CAT lookups must be numeric.');
558
559 end;
560
561 -- First populate the cache tables, if necessary
562 -- Note: No check is made for the validity of the table data, since it is assumed that the
563 -- reset_tables procedure was called before this procedure.
564 -- This does not actually calculate the value, it just calls the lowest procedure
565 -- to populate the cache
566 if p_occupational_type = 'LEV' then
567
568 -- Check whether the averages for the current occupational level already exist.
569 if not g_lev_averages_table.exists(l_index) then
570
571 hr_utility.set_location('Step ' || l_proc, 20);
572 l_avg_5_highest_salary := get_avg_5_lowest_salary
573 (
574 p_report_date => p_report_date,
575 p_business_group_id => p_business_group_id,
576 p_legal_entity_id => p_legal_entity_id,
577 p_occupational_level_cat => p_occupational_level_cat,
578 p_lookup_code => p_lookup_code,
579 p_occupational_type => p_occupational_type,
580 p_salary_method => p_salary_method
581 );
582 end if;
583
584 elsif p_occupational_type = 'CAT' then
585
586 -- Check whether the averages for the current occupational category already exist.
587 if not g_cat_averages_table.exists(l_index) then
588
589 hr_utility.set_location('Step ' || l_proc, 23);
590 l_avg_5_highest_salary := get_avg_5_lowest_salary
591 (
592 p_report_date => p_report_date,
593 p_business_group_id => p_business_group_id,
594 p_legal_entity_id => p_legal_entity_id,
595 p_occupational_level_cat => p_occupational_level_cat,
596 p_lookup_code => p_lookup_code,
597 p_occupational_type => p_occupational_type,
598 p_salary_method => p_salary_method
599 );
600 end if;
601
602 elsif p_occupational_type is null then
603
604 -- Check whether the averages already exist.
605 if g_all_high_avg = -9999 then
606
607 hr_utility.set_location('Step ' || l_proc, 24);
608 l_avg_5_highest_salary := get_avg_5_lowest_salary
609 (
610 p_report_date => p_report_date,
611 p_business_group_id => p_business_group_id,
612 p_legal_entity_id => p_legal_entity_id,
613 p_occupational_level_cat => p_occupational_level_cat,
614 p_lookup_code => p_lookup_code,
615 p_occupational_type => p_occupational_type,
616 p_salary_method => p_salary_method
617 );
618
619 end if;
620
621 end if;
622
623 hr_utility.set_location('Lookup Code ' || p_lookup_code, 25);
624
625 -- Check Occupational Type
626 if p_occupational_type = 'LEV' then
627
628 l_avg_5_highest_salary := g_lev_averages_table(l_index).high;
629 hr_utility.set_location('LEV ' || to_char(l_avg_5_highest_salary), 30);
630
631 elsif p_occupational_type = 'CAT' then
632
633 l_avg_5_highest_salary := g_cat_averages_table(l_index).high;
634 hr_utility.set_location('CAT ' || to_char(l_avg_5_highest_salary), 40);
635
636 -- Average 5 highest salaries for all employees, irrespective of category or levels
637 -- elsif p_occupational_type not in ('CAT','LEV')
638 elsif p_occupational_type is null then
639
640 l_avg_5_highest_salary := g_all_high_avg;
641 hr_utility.set_location('TOTAL ' || to_char(l_avg_5_highest_salary), 50);
642
643 end if;
644
645 hr_utility.set_location('Exiting ' || l_proc, 60);
646 return l_avg_5_highest_salary;
647
648 end get_avg_5_highest_salary;
649
650 -- This function returns the average 5 lowest paid employees per category or level.
651 function get_avg_5_lowest_salary
652 (
653 p_report_date in per_all_assignments_f.effective_end_date%type,
654 p_business_group_id in per_all_assignments_f.business_group_id%type,
655 p_legent_param in per_assignment_extra_info.aei_information7%type := null,
656 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
657 p_occupational_level_cat in hr_lookups.meaning%type,
658 p_lookup_code in hr_lookups.lookup_code%type,
659 p_occupational_type in varchar2, -- LEV = Levels, CAT = Categories
660 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
661 ) return number is
662
663 cursor c_assignments is
664 select paaf.assignment_id,
665 paaf.person_id, -- Bug 4413678
666 paaf.payroll_id,
667 paei.aei_information7,
668 per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
669 per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
670 paaf.pay_basis_id
671 from per_assignment_extra_info paei,
672 per_assignment_status_types past,
673 per_all_assignments_f paaf
674 where paaf.business_group_id = p_business_group_id
675 and p_report_date between paaf.effective_start_date and paaf.effective_end_date
676 and past.assignment_status_type_id = paaf.assignment_status_type_id
677 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
678 and paei.assignment_id = paaf.assignment_id
679 and paei.information_type = 'ZA_SPECIFIC_INFO'
680 and paei.aei_information7 = nvl(p_legent_param, paei.aei_information7)
681 and paei.aei_information7 is not null
682 and nvl(paei.aei_information6, 'N') <> 'Y'
683 order by paaf.payroll_id;
684
685 l_avg_5_lowest_salary number;
686 l_old_payroll_id per_all_assignments_f.payroll_id%type := -9999;
687 l_rowind pls_integer;
688 l_active_days number;
689 l_ee_income number;
690 l_ee_annual_income number;
691 l_report_start date;
692 l_report_end date;
693 l_report_date date;
694 l_difference number;
695 l_period_frequency per_time_period_types.number_per_fiscal_year%type;
696 l_ee_balance_type_id pay_balance_types.balance_type_id%type;
697 l_eea_balance_type_id pay_balance_types.balance_type_id%type;
698 l_input_value_id pay_input_values_f.input_value_id%type;
699 l_index number;
700 l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
701 l_race per_all_people_f.per_information4%type; -- Bug 4413678
702
703 begin
704
705 hr_utility.set_location('Entering ' || l_proc, 10);
706
707 -- Determine whether we need to populate the cache tables
708 -- Note: No check is made for the validity of the table data, since it is assumed that the
709 -- reset_tables procedure was called before this procedure.
710 if g_assignments_table.count = 0 then
711
712 hr_utility.set_location('Setup assignments cache', 20);
713 g_assignments_table.delete;
714
715 if p_salary_method = 'BAL' then
716
717 -- Get the balance type id's for the normal and annual balances
718 begin
719
720 select balance_type_id
721 into l_ee_balance_type_id
722 from pay_balance_types
723 where balance_name = 'Total Employment Equityable Income'
724 and legislation_code = 'ZA'
725 and business_group_id is null;
726
727 select balance_type_id
728 into l_eea_balance_type_id
729 from pay_balance_types
730 where balance_name = 'Total Employment Equityable Annual Income'
731 and legislation_code = 'ZA'
732 and business_group_id is null;
733
734 exception
735 when no_data_found then
736 raise_application_error(-20000, 'The Employment Equitable balances do not exist.');
737
738 end;
739
740 end if;
741
742 if p_salary_method = 'ELE' then
743
744 -- Get the ZA Employment Equity Remuneration element details
745 begin
746
747 select pivf.input_value_id
748 into l_input_value_id
749 from pay_input_values_f pivf,
750 pay_element_types_f petf
751 where petf.element_name = 'ZA Employment Equity Remuneration'
752 and petf.business_group_id is null
753 and petf.legislation_code = 'ZA'
754 and p_report_date between petf.effective_start_date and petf.effective_end_date
755 and pivf.element_type_id = petf.element_type_id
756 and pivf.name = 'Remuneration'
757 and p_report_date between pivf.effective_start_date and pivf.effective_end_date;
758
759 exception
760 when no_data_found then
761 raise_application_error(-20004, 'The ZA Employment Equity Remuneration element does not exist.');
762
763 end;
764
765 end if;
766
767 -- Loop through the assignments cursor and populate the assignments table
768 for l_assignment in c_assignments loop
769
770 hr_utility.set_location('ASG ' || l_assignment.assignment_id, 21);
771
772 -- Bug 4413678: Begin
773 Select per_information4
774 into l_race
775 From per_all_people_f papf
776 Where papf.person_id = l_assignment.person_id
777 and p_report_date between papf.effective_start_date and papf.effective_end_date;
778 -- Bug 4413678: End
779
780
781 if l_assignment.payroll_id is not null and l_race <> 'N' then -- Bug 4413678: Added l_race <> 'Not Used'
782
783 g_assignments_table(l_assignment.assignment_id).payroll_id := l_assignment.payroll_id;
784 g_assignments_table(l_assignment.assignment_id).legal_entity_id := l_assignment.aei_information7;
785 g_assignments_table(l_assignment.assignment_id).occupational_level := l_assignment.occupational_level;
786 g_assignments_table(l_assignment.assignment_id).occupational_category := l_assignment.occupational_category;
787
788 hr_utility.set_location('LEGENT ' || l_assignment.aei_information7, 22);
789
790 -- Check for a new payroll_id and cache the new payroll details in the payrolls table
791 if l_assignment.payroll_id <> l_old_payroll_id then
792
793 -- Get the start date and end date of the report
794 begin
795
796
797 l_report_date := p_report_date;
798 l_difference := 0;
799
800 while (l_difference < 355 or l_difference > 375) loop
801
802 select ptpf.end_date + 1,
803 ptpl.end_date
804 into l_report_start,
805 l_report_end
806 from per_time_periods ptpf,
807 per_time_periods ptpl
808 where ptpl.payroll_id = l_assignment.payroll_id
809 and l_report_date between ptpl.start_date and ptpl.end_date
810 and ptpf.payroll_id = l_assignment.payroll_id
811 and add_months(l_report_date, -12) + 1 between ptpf.start_date and ptpf.end_date;
812
813 l_difference := l_report_end - l_report_start + 1;
814
815 if (l_difference < 355 or l_difference > 375) then
816
817 l_report_date := l_report_date - 1;
818
819 end if;
820
821 end loop;
822
823 exception
824 when no_data_found then
825 begin
826 select ptpl.end_date
827 into l_report_end
828 from per_time_periods ptpl
829 where ptpl.payroll_id = l_assignment.payroll_id
830 and p_report_date between ptpl.start_date and ptpl.end_date;
831
832 exception
833 when no_data_found then
834 Null;
835 end;
836
837 l_report_start := add_months(l_report_end, -12) + 1;
838
839 end;
840
841 -- Get the payroll period frequency
842 begin
843
844 select ptpt.number_per_fiscal_year
845 into l_period_frequency
846 from per_time_period_types ptpt,
847 pay_all_payrolls_f payr
848 where payr.payroll_id = l_assignment.payroll_id
849 and p_report_date between payr.effective_start_date and payr.effective_end_date
850 and ptpt.period_type = payr.period_type;
851
852 exception
853 when no_data_found then
854 raise_application_error(-20005, 'The Payroll Period Frequency does not exist.');
855
856 end;
857
858 l_old_payroll_id := l_assignment.payroll_id;
859
860 end if;
861
862 hr_utility.set_location('REP_START ' || to_char(l_report_start, 'DD\MM\YYYY'), 22);
863 hr_utility.set_location('REP_END ' || to_char(l_report_end, 'DD\MM\YYYY'), 23);
864 hr_utility.set_location('FREQ ' || l_period_frequency, 24);
865
866 if p_salary_method = 'BAL' then
867
868 -- Get the amount of days the assignment status was Active Assignment
869 l_active_days := get_active_days
870 (
871 p_assignment_id => l_assignment.assignment_id,
872 p_report_start => l_report_start,
873 p_report_end => l_report_end
874 );
875
876 hr_utility.set_location('ACT_DAYS ' || l_active_days, 25);
877
878 -- Get the Employment Equitable Income
879 begin
880
881 select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
882 into l_ee_income
883 from pay_balance_feeds_f pbff,
884 pay_run_result_values prrv,
885 pay_run_results prr,
886 pay_payroll_actions ppa,
887 pay_assignment_actions paa,
888 per_assignments_f asg --Bug 4872110
889 -- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
890 where paa.assignment_id = l_assignment.assignment_id
891 and ppa.payroll_action_id = paa.payroll_action_id
892 and ppa.date_earned between l_report_start and l_report_end
893 and prr.assignment_action_id = paa.assignment_action_id
894 and prrv.run_result_id = prr.run_result_id
895 and pbff.balance_type_id = l_ee_balance_type_id
896 and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
897 and prrv.input_value_id = pbff.input_value_id
898 -- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
899 and paa.assignment_id = asg.assignment_id
900 and asg.payroll_id = ppa.payroll_id;
901
902 exception
903 when no_data_found then
904 l_ee_income := 0;
905
906 end;
907
908 -- Get the Employment Equitable Annual Income
909 begin
910
911 select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
912 into l_ee_annual_income
913 from pay_balance_feeds_f pbff,
914 pay_run_result_values prrv,
915 pay_run_results prr,
916 pay_payroll_actions ppa,
917 pay_assignment_actions paa,
918 per_assignments_f asg --Bug 4872110
919 -- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
920 where paa.assignment_id = l_assignment.assignment_id
921 and ppa.payroll_action_id = paa.payroll_action_id
922 and ppa.date_earned between l_report_start and l_report_end
923 and prr.assignment_action_id = paa.assignment_action_id
924 and prrv.run_result_id = prr.run_result_id
925 and pbff.balance_type_id = l_eea_balance_type_id
926 and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
927 and prrv.input_value_id = pbff.input_value_id
928 -- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
929 and paa.assignment_id = asg.assignment_id
930 and asg.payroll_id = ppa.payroll_id;
931
932 exception
933 when no_data_found then
934 l_ee_annual_income := 0;
935
936 end;
937
938 hr_utility.set_location('EE_INC ' || l_ee_income, 26);
939 hr_utility.set_location('EE_ANN ' || l_ee_annual_income, 27);
940 hr_utility.set_location('STminEND ' || (l_report_end - l_report_start + 1), 28);
941
942 -- Calculate the annual income = annualize normal income + annual income
943 g_assignments_table(l_assignment.assignment_id).annual_income :=
944 (
945 (l_report_end - l_report_start + 1) / l_active_days * l_ee_income
946 ) + l_ee_annual_income;
947
948 hr_utility.set_location('ANSWER ' || g_assignments_table(l_assignment.assignment_id).annual_income, 29);
949
950 elsif p_salary_method = 'SAL' then
951
952 -- Get the annual salary basis for the current period
953 begin
954
955 select ppp.proposed_salary_n * ppb.pay_annualization_factor
956 into g_assignments_table(l_assignment.assignment_id).annual_income
957 from per_pay_proposals ppp,
958 per_pay_bases ppb
959 where ppb.pay_basis_id = l_assignment.pay_basis_id
960 and ppp.assignment_id = l_assignment.assignment_id
961 and ppp.approved = 'Y'
962 and ppp.change_date =
963 (
964 select max(ppp2.change_date)
965 from per_pay_proposals ppp2
966 where ppp2.assignment_id = l_assignment.assignment_id
967 and ppp2.change_date <= p_report_date
968 and ppp2.approved = 'Y'
969 );
970
971 exception
972 when no_data_found then
973 g_assignments_table(l_assignment.assignment_id).annual_income := 0;
974
975 end;
976
977 elsif p_salary_method = 'ELE' then
978
979 begin
980
981 select peevf.screen_entry_value * l_period_frequency
982 into g_assignments_table(l_assignment.assignment_id).annual_income
983 from pay_element_entry_values_f peevf,
984 pay_element_entries_f peef
985 where peef.assignment_id = l_assignment.assignment_id
986 and p_report_date between peef.effective_start_date and peef.effective_end_date
987 and peevf.element_entry_id = peef.element_entry_id
988 and peevf.input_value_id = l_input_value_id
989 and p_report_date between peevf.effective_start_date and peevf.effective_end_date;
990
991 exception
992 when no_data_found then
993 g_assignments_table(l_assignment.assignment_id).annual_income := 0;
994
995 end;
996
997 end if; -- p_salary_method
998
999 end if; -- (l_assignment.payroll_id is not null)
1000
1001 end loop; -- c_assignments
1002
1003 end if; -- g_assignments_table.count = 0
1004
1005 -- The index is calculted by multiplying the legal entity id by 100 and then adding the lookup code
1006 -- This should always give a unique value, since the lookup code is less than 100
1007 begin
1008
1009 l_index := p_legal_entity_id * 100 + p_lookup_code;
1010 hr_utility.set_location('LEV/CAT INDEX ' || l_index, 25);
1011
1012 exception
1013 when others then
1014 raise_application_error(-20006, 'The lookup code in the ZA_EMP_EQ_OCCUPATIONAL_LEV and ZA_EMP_EQ_OCCUPATIONAL_CAT lookups must be numeric.');
1015
1016 end;
1017
1018 if p_occupational_type = 'LEV' then
1019
1020 hr_utility.set_location('LEV cache check' || p_lookup_code, 30);
1021
1022 -- Check whether the averages for the current occupational level already exist.
1023 if not g_lev_averages_table.exists(l_index) then
1024
1025 hr_utility.set_location('LEV cache' || p_lookup_code, 40);
1026 reset_high_low_lists;
1027
1028 -- Loop through assignments cache table to look for current occupational level
1029 l_rowind := g_assignments_table.first;
1030 loop
1031
1032 exit when l_rowind is null;
1033
1034 -- If the occupational category of the assignment is the same as the one we are
1035 -- looking for then add the value to the highest and lowest list
1036 if g_assignments_table(l_rowind).occupational_level = p_occupational_level_cat
1037 and g_assignments_table(l_rowind).legal_entity_id = p_legal_entity_id then
1038
1039 get_highest_and_lowest(g_assignments_table(l_rowind).annual_income);
1040
1041 end if;
1042
1043 l_rowind := g_assignments_table.next(l_rowind);
1044
1045 end loop;
1046
1047 -- Calculate the average of the 5 highest and lowest values in the list,
1048 -- and add the answers to the occupational level average cache table
1049 calc_highest_and_lowest_avg
1050 (
1051 g_lev_averages_table(l_index).high,
1052 g_lev_averages_table(l_index).low
1053 );
1054 l_avg_5_lowest_salary := g_lev_averages_table(l_index).low;
1055
1056 else
1057
1058 l_avg_5_lowest_salary := g_lev_averages_table(l_index).low;
1059
1060 end if;
1061
1062 elsif p_occupational_type = 'CAT' then
1063
1064 hr_utility.set_location('CAT cache check' || p_lookup_code, 50);
1065
1066 -- Check whether the averages for the current occupational category already exist.
1067 if not g_cat_averages_table.exists(l_index) then
1068
1069 hr_utility.set_location('CAT cache' || p_lookup_code, 60);
1070 reset_high_low_lists;
1071
1072 -- Loop through assignments cache table to look for current occupational category
1073 l_rowind := g_assignments_table.first;
1074 loop
1075
1076 exit when l_rowind is null;
1077
1078 -- If the occupational category of the assignment is the same as the one we are
1079 -- looking for then add the value to the highest and lowest list
1080 if g_assignments_table(l_rowind).occupational_category = p_occupational_level_cat
1081 and g_assignments_table(l_rowind).legal_entity_id = p_legal_entity_id then
1082
1083 get_highest_and_lowest(g_assignments_table(l_rowind).annual_income);
1084
1085 end if;
1086
1087 l_rowind := g_assignments_table.next(l_rowind);
1088
1089 end loop;
1090
1091 -- Calculate the average of the 5 highest and lowest values in the list,
1092 -- and add the answers to the occupational category average cache table
1093 calc_highest_and_lowest_avg
1094 (
1095 g_cat_averages_table(l_index).high,
1096 g_cat_averages_table(l_index).low
1097 );
1098 l_avg_5_lowest_salary := g_cat_averages_table(l_index).low;
1099
1100 else
1101
1102 l_avg_5_lowest_salary := g_cat_averages_table(l_index).low;
1103
1104 end if;
1105
1106 -- elsif p_occupational_type not in ('CAT', 'LEV')
1107 elsif p_occupational_type is null then
1108
1109 hr_utility.set_location('TOTAL cache check', 50);
1110
1111 -- Check whether the averages already exist.
1112 if g_all_high_avg = -9999 then
1113
1114 hr_utility.set_location('TOTAL cache', 60);
1115 reset_high_low_lists;
1116
1117 -- Loop through assignments cache table to look for current occupational category
1118 l_rowind := g_assignments_table.first;
1119 loop
1120
1121 exit when l_rowind is null;
1122
1123 -- Add the value to the highest and lowest list
1124 get_highest_and_lowest(g_assignments_table(l_rowind).annual_income);
1125
1126 l_rowind := g_assignments_table.next(l_rowind);
1127
1128 end loop;
1129
1130 -- Calculate the average of the 5 highest and lowest values in the list,
1131 -- and add the answers to the occupational level average cache table
1132 calc_highest_and_lowest_avg
1133 (
1134 g_all_high_avg,
1135 g_all_low_avg
1136 );
1137 l_avg_5_lowest_salary := g_all_low_avg;
1138
1139 else
1140
1141 l_avg_5_lowest_salary := g_all_low_avg;
1142
1143 end if;
1144
1145 end if;
1146
1147 return l_avg_5_lowest_salary;
1148
1149 end get_avg_5_lowest_salary;
1150
1151 -- This function returns the person's legislated employment type (permanent or non-permanent)
1152 -- The employee has to work for a continuous period of 3 months in order to be seen as permanent
1153 function get_ee_employment_type_name
1154 (
1155 p_report_date in per_all_people_f.start_date%type,
1156 p_period_of_service_id in per_all_assignments_f.period_of_service_id%type
1157 ) return varchar2 is
1158
1159 l_ee_employment_type_name varchar2(13);
1160 l_date_start per_periods_of_service.date_start%type;
1161 l_date_end date;
1162 l_actual_termination_date per_periods_of_service.actual_termination_date%type;
1163 l_projected_termination_date per_periods_of_service.projected_termination_date%type;
1164
1165 begin
1166
1167 select date_start,
1168 actual_termination_date,
1169 projected_termination_date
1170 into l_date_start,
1171 l_actual_termination_date,
1172 l_projected_termination_date
1173 from per_periods_of_service
1174 where period_of_service_id = p_period_of_service_id;
1175
1176 if l_actual_termination_date is null then
1177
1178 if l_projected_termination_date is null then
1179
1180 l_date_end := to_date('30-12-4712', 'DD-MM-YYYY');
1181
1182 else
1183
1184 -- If the report date is after the projected termination date,
1185 -- then the projected termination date is discarded
1186 if p_report_date > l_projected_termination_date then
1187
1188 l_date_end := to_date('30-12-4712', 'DD-MM-YYYY');
1189
1190 else -- Use the projected termination date
1191
1192 l_date_end := l_projected_termination_date;
1193
1194 end if;
1195
1196 end if;
1197
1198 else
1199
1200 l_date_end := l_actual_termination_date;
1201
1202 end if;
1203
1204 if months_between(l_date_end + 1, l_date_start) < 3 then
1205 l_ee_employment_type_name := 'Non-Permanent';
1206 else
1207 l_ee_employment_type_name := 'Permanent';
1208 end if;
1209
1210 return l_ee_employment_type_name;
1211
1212 exception
1213 when no_data_found then
1214 return 'Non-Permanent';
1215
1216 end get_ee_employment_type_name;
1217
1218 -- This function returns the occupational category from the common lookups table.
1219 function get_occupational_category
1220 (
1221 p_report_date in per_all_assignments_f.effective_end_date%type,
1222 p_assignment_id in per_all_assignments_f.assignment_id%type,
1223 p_job_id in per_all_assignments_f.job_id%type,
1224 p_grade_id in per_all_assignments_f.grade_id%type,
1225 p_position_id in per_all_assignments_f.position_id%type,
1226 p_business_group_id in per_all_assignments_f.business_group_id%type
1227 ) return varchar2 is
1228
1229 begin
1230
1231 -- Check whether we have cached the location of Occupational data
1232 if g_cat_flex is null then
1233
1234 cache_occupational_location(p_report_date, p_business_group_id);
1235
1236 end if;
1237
1238 -- Check whether the current assignment's value is cached already
1239 if p_report_date = g_cat_report_date
1240 and p_assignment_id = g_cat_asg_id then
1241
1242 return g_cat_name;
1243
1244 else
1245
1246 g_cat_report_date := p_report_date;
1247 g_cat_asg_id := p_assignment_id;
1248
1249 g_cat_name := get_occupational_data
1250 (
1251 p_type => 'CAT',
1252 p_flex => g_cat_flex,
1253 p_segment => g_cat_segment,
1254 p_job_id => p_job_id,
1255 p_grade_id => p_grade_id,
1256 p_position_id => p_position_id
1257 );
1258
1259 return g_cat_name;
1260
1261 end if;
1262
1263 end get_occupational_category;
1264
1265 -- This function returns the occupational levels from the user tables.
1266 function get_occupational_level
1267 (
1268 p_report_date in per_all_assignments_f.effective_end_date%type,
1269 p_assignment_id in per_all_assignments_f.assignment_id%type,
1270 p_job_id in per_all_assignments_f.job_id%type,
1271 p_grade_id in per_all_assignments_f.grade_id%type,
1272 p_position_id in per_all_assignments_f.position_id%type,
1273 p_business_group_id in per_all_assignments_f.business_group_id%type
1274 ) return varchar2 is
1275
1276 begin
1277
1278 -- Check whether we have cached the location of Occupational data
1279 if g_lev_flex is null then
1280
1281 cache_occupational_location(p_report_date, p_business_group_id);
1282
1283 end if;
1284
1285 -- Check whether the current assignment's value is cached already
1286 if p_report_date = g_lev_report_date
1287 and p_assignment_id = g_lev_asg_id then
1288
1289 return g_lev_name;
1290
1291 else
1292
1293 g_lev_report_date := p_report_date;
1294 g_lev_asg_id := p_assignment_id;
1295
1296 g_lev_name := get_occupational_data
1297 (
1298 p_type => 'LEV',
1299 p_flex => g_lev_flex,
1300 p_segment => g_lev_segment,
1301 p_job_id => p_job_id,
1302 p_grade_id => p_grade_id,
1303 p_position_id => p_position_id
1304 );
1305
1306 return g_lev_name;
1307
1308 end if;
1309
1310 end get_occupational_level;
1311
1312 function get_occupational_cat_data
1313 (
1314 p_type in varchar2,
1315 p_flex in varchar2,
1316 p_segment in varchar2,
1317 p_job_id in per_all_assignments_f.job_id%type,
1318 p_grade_id in per_all_assignments_f.grade_id%type,
1319 p_position_id in per_all_assignments_f.position_id%type
1320 ) return varchar2 is
1321
1322 l_name hr_lookups.meaning%type;
1323 l_sql varchar2(32767);
1324 begin
1325
1326 hr_utility.set_location('p_job_id '||p_job_id , 30);
1327 hr_utility.set_location('p_grade_id '||p_grade_id , 30);
1328 hr_utility.set_location('p_position_id '||p_position_id , 30);
1329
1330 if p_flex = 'Job' then
1331 begin
1332
1333 if p_job_id is not null then
1334 -- hr_utility.set_location('Security_grp_Id :' || fnd_global.lookup_security_group('ZA_WSP_OCCUPATIONAL_CATEGORIES',3),30);
1335 l_sql := 'select decode(flv1.attribute1,null,flv1.meaning, flv2.meaning) from fnd_lookup_values flv1, fnd_lookup_values flv2, per_job_definitions pjd, per_jobs pj where pj.job_id = '
1336 || to_char(p_job_id)
1337 || ' and pjd.job_definition_id = pj.job_definition_id '
1338 || ' and flv1.lookup_type = '||'''ZA_WSP_OCCUPATIONAL_CATEGORIES'''
1339 || ' and flv1.lookup_code = pjd.' || p_segment
1340 || ' and flv2.lookup_type(+) = '||'''ZA_EMP_EQ_OCCUPATIONAL_CAT'''
1341 || ' and flv2.lookup_code(+) = flv1.attribute1'
1342 || ' and flv1.security_group_id = fnd_global.lookup_security_group(flv1.lookup_type,3)'
1343 || ' and flv1.language = userenv('||'''LANG'''||')'
1344 || ' and flv2.language(+) = userenv('||'''LANG'''||')';
1345
1346
1347 execute immediate l_sql into l_name;
1348 else
1349
1350 l_name := null;
1351
1352 end if;
1353
1354 exception
1355 when no_data_found then
1356 l_name := null;
1357
1358 end;
1359
1360 elsif p_flex = 'Grade' then
1361
1362 begin
1363 if p_grade_id is not null then
1364
1365 l_sql := 'select decode(flv1.attribute1,null,flv1.meaning, flv2.meaning) from fnd_lookup_values flv1, fnd_lookup_values flv2, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
1366 || to_char(p_grade_id)
1367 || ' and pgd.grade_definition_id = pg.grade_definition_id '
1368 || ' and flv1.lookup_type = '||'''ZA_WSP_OCCUPATIONAL_CATEGORIES'''
1369 || ' and flv1.lookup_code = pgd.' || p_segment
1370 || ' and flv2.lookup_type(+) = '||'''ZA_EMP_EQ_OCCUPATIONAL_CAT'''
1371 || ' and flv2.lookup_code(+) = flv1.attribute1'
1372 || ' and flv1.security_group_id = fnd_global.lookup_security_group(flv1.lookup_type,3)'
1373 || ' and flv1.language = userenv('||'''LANG'''||')'
1374 || ' and flv2.language(+) = userenv('||'''LANG'''||')';
1375
1376 execute immediate l_sql into l_name;
1377
1378 else
1379
1380 l_name := null;
1381
1382 end if;
1383
1384 exception
1385 when no_data_found then
1386 l_name := null;
1387
1388 end;
1389
1390 elsif p_flex = 'Position' then
1391
1392 begin
1393 if p_position_id is not null then
1394
1395 l_sql := 'select decode(flv1.attribute1,null,flv1.meaning, flv2.meaning) from fnd_lookup_values flv1, fnd_lookup_values flv2, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
1396 || to_char(p_position_id)
1397 || ' and ppd.position_definition_id = pap.position_definition_id '
1398 || ' and flv1.lookup_type = '||'''ZA_WSP_OCCUPATIONAL_CATEGORIES'''
1399 || ' and flv1.lookup_code = ppd.' || p_segment
1400 || ' and flv2.lookup_type(+) = '||'''ZA_EMP_EQ_OCCUPATIONAL_CAT'''
1401 || ' and flv2.lookup_code(+) = flv1.attribute1'
1402 || ' and flv1.security_group_id = fnd_global.lookup_security_group(flv1.lookup_type,3)'
1403 || ' and flv1.language = userenv('||'''LANG'''||')'
1404 || ' and flv2.language(+) = userenv('||'''LANG'''||')';
1405
1406
1407 execute immediate l_sql into l_name;
1408
1409 else
1410
1411 l_name := null;
1412
1413 end if;
1414
1415 exception
1416 when no_data_found then
1417 l_name := null;
1418
1419 end;
1420
1421 else
1422
1423 raise_application_error(-20002, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Flexfield.');
1424
1425 end if;
1426
1427 RETURN l_name;
1428
1429 END get_occupational_cat_data;
1430
1431
1432 -- This function retrieves the occupational data via dynamic sql from the appropriate flexfield segment
1433 /*
1434 08-Jan-2008
1435 Logic for changes in the get_occupational_data
1436 if the p_type = 'CAT' -- Category
1437 check if the lookup_type 'ZA_WSP_OCCUPATIONAL_CATEGORIES' present in db.
1438 if it is present the Grade/Job/Position flexfields will have the
1439 cotegory code from the ZA_WSP_OCCUPATIONAL_CATEGORIES stored on the flexfield.
1440 And the corresponding the Employment Equity code we have to get from
1441 ZA_EMP_EQ_OCCUPATIONAL_CAT .
1442
1443 */
1444
1445 function get_occupational_data
1446 (
1447 p_type in varchar2,
1448 p_flex in varchar2,
1449 p_segment in varchar2,
1450 p_job_id in per_all_assignments_f.job_id%type,
1451 p_grade_id in per_all_assignments_f.grade_id%type,
1452 p_position_id in per_all_assignments_f.position_id%type
1453 ) return varchar2 is
1454
1455 l_sql varchar2(32767);
1456 l_indicator NUMBER;
1457 l_name hr_lookups.meaning%type;
1458 l_code hr_lookups.lookup_code%type;
1459 l_lookup_type hr_lookups.lookup_type%type;
1460
1461 begin
1462 -- Added 08-Jan-2008
1463 l_indicator := 0;
1464 hr_utility.set_location('p_type '||p_type,20);
1465 hr_utility.set_location('p_flex '||p_flex,20);
1466 hr_utility.set_location('p_segment'||p_segment,20);
1467 IF p_type = 'CAT' then
1468 Select COUNT(*)
1469 INTO l_indicator
1470 FROM hr_lookups
1471 WHERE lookup_type = 'ZA_WSP_OCCUPATIONAL_CATEGORIES';
1472 END IF ;
1473
1474 hr_utility.set_location('l_indicator'||l_indicator, 20);
1475
1476 IF l_indicator > 0 THEN
1477 l_name := get_occupational_cat_data
1478 (
1479 p_type => p_type,
1480 p_flex => p_flex,
1481 p_segment => p_segment,
1482 p_job_id => p_job_id,
1483 p_grade_id => p_grade_id,
1484 p_position_id => p_position_id
1485 );
1486
1487 RETURN l_name;
1488 END IF ;
1489 --End
1490
1491 if p_flex = 'Job' then
1492
1493 begin
1494 if p_job_id is not null then
1495 l_sql := 'select hl.meaning from hr_lookups hl, per_job_definitions pjd, per_jobs pj where pj.job_id = '
1496 || to_char(p_job_id)
1497 || ' and pjd.job_definition_id = pj.job_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EMP_EQ_OCCUPATIONAL_'
1498 || p_type || ''' and hl.lookup_code = pjd.' || p_segment;
1499
1500 execute immediate l_sql into l_name;
1501
1502 else
1503
1504 l_name := null;
1505
1506 end if;
1507
1508 exception
1509 when no_data_found then
1510 l_name := null;
1511
1512 end;
1513
1514 elsif p_flex = 'Grade' then
1515
1516 begin
1517 if p_grade_id is not null then
1518 l_sql := 'select hl.meaning from hr_lookups hl, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
1519 || to_char(p_grade_id)
1520 || ' and pgd.grade_definition_id = pg.grade_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EMP_EQ_OCCUPATIONAL_'
1521 || p_type || ''' and hl.lookup_code = pgd.' || p_segment;
1522
1523 execute immediate l_sql into l_name;
1524
1525 else
1526
1527 l_name := null;
1528
1529 end if;
1530
1531 exception
1532 when no_data_found then
1533 l_name := null;
1534
1535 end;
1536
1537 elsif p_flex = 'Position' then
1538
1539 begin
1540 if p_position_id is not null then
1541 l_sql := 'select hl.meaning from hr_lookups hl, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
1542 || to_char(p_position_id)
1543 || ' and ppd.position_definition_id = pap.position_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EMP_EQ_OCCUPATIONAL_'
1544 || p_type || ''' and hl.lookup_code = ppd.' || p_segment;
1545
1546 execute immediate l_sql into l_name;
1547
1548 else
1549
1550 l_name := null;
1551
1552 end if;
1553
1554 exception
1555 when no_data_found then
1556 l_name := null;
1557
1558 end;
1559
1560 else
1561
1562 raise_application_error(-20002, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Flexfield.');
1563
1564 end if;
1565
1566 return l_name;
1567
1568 end get_occupational_data;
1569
1570 -- This procedure caches the location of the occupational category and level data.
1571 procedure cache_occupational_location
1572 (
1573 p_report_date in date,
1574 p_business_group_id in per_all_assignments_f.business_group_id%type
1575 ) is
1576
1577 l_user_table_id pay_user_tables.user_table_id%type;
1578 l_user_column_id_flex pay_user_columns.user_column_id%type;
1579 l_user_column_id_seg pay_user_columns.user_column_id%type;
1580 l_user_row_id_cat pay_user_rows_f.user_row_id%type;
1581 l_user_row_id_lev pay_user_rows_f.user_row_id%type;
1582 l_user_row_id_func pay_user_rows_f.user_row_id%type;
1583 l_temp varchar2(9);
1584
1585 begin
1586 -- hr_utility.trace_on(null,'PERZAEER');
1587
1588 select user_table_id
1589 into l_user_table_id
1590 from pay_user_tables
1591 where user_table_name = 'ZA_OCCUPATIONAL_TYPES'
1592 and business_group_id is null
1593 and legislation_code = 'ZA';
1594
1595 hr_utility.set_location('l_user_table_id'||l_user_table_id, 10);
1596
1597 select user_column_id
1598 into l_user_column_id_flex
1599 from pay_user_columns
1600 where user_table_id = l_user_table_id
1601 and business_group_id is null
1602 and legislation_code = 'ZA'
1603 and user_column_name = 'Flexfield';
1604
1605 select user_column_id
1606 into l_user_column_id_seg
1607 from pay_user_columns
1608 where user_table_id = l_user_table_id
1609 and business_group_id is null
1610 and legislation_code = 'ZA'
1611 and user_column_name = 'Segment';
1612
1613 select user_row_id
1614 into l_user_row_id_cat
1615 from pay_user_rows_f
1616 where user_table_id = l_user_table_id
1617 and row_low_range_or_name = 'Occupational Categories'
1618 and p_report_date between effective_start_date and effective_end_date;
1619
1620 select user_row_id
1621 into l_user_row_id_lev
1622 from pay_user_rows_f
1623 where user_table_id = l_user_table_id
1624 and row_low_range_or_name = 'Occupational Levels'
1625 and p_report_date between effective_start_date and effective_end_date;
1626
1627 select user_row_id
1628 into l_user_row_id_func
1629 from pay_user_rows_f
1630 where user_table_id = l_user_table_id
1631 and row_low_range_or_name = 'Function Type'
1632 and p_report_date between effective_start_date and effective_end_date;
1633
1634
1635 select value
1636 into g_cat_flex
1637 from pay_user_column_instances_f
1638 where user_row_id = l_user_row_id_cat
1639 and user_column_id = l_user_column_id_flex
1640 and business_group_id = p_business_group_id
1641 and p_report_date between effective_start_date and effective_end_date;
1642
1643 select value
1644 into g_lev_flex
1645 from pay_user_column_instances_f
1646 where user_row_id = l_user_row_id_lev
1647 and user_column_id = l_user_column_id_flex
1648 and business_group_id = p_business_group_id
1649 and p_report_date between effective_start_date and effective_end_date;
1650
1651 select value
1652 into g_Func_flex
1653 from pay_user_column_instances_f
1654 where user_row_id = l_user_row_id_func
1655 and user_column_id = l_user_column_id_flex
1656 and business_group_id = p_business_group_id
1657 and p_report_date between effective_start_date and effective_end_date;
1658
1659 select value
1660 into g_lev_segment
1661 from pay_user_column_instances_f
1662 where user_row_id = l_user_row_id_lev
1663 and user_column_id = l_user_column_id_seg
1664 and business_group_id = p_business_group_id
1665 and p_report_date between effective_start_date and effective_end_date;
1666
1667 select value
1668 into g_cat_segment
1669 from pay_user_column_instances_f
1670 where user_row_id = l_user_row_id_cat
1671 and user_column_id = l_user_column_id_seg
1672 and business_group_id = p_business_group_id
1673 and p_report_date between effective_start_date and effective_end_date;
1674
1675 select value
1676 into g_Func_segment
1677 from pay_user_column_instances_f
1678 where user_row_id = l_user_row_id_func
1679 and user_column_id = l_user_column_id_seg
1680 and business_group_id = p_business_group_id
1681 and p_report_date between effective_start_date and effective_end_date;
1682
1683 hr_utility.set_location('l_user_table_id'||l_user_table_id, 10);
1684 hr_utility.set_location('l_user_column_id_flex'||l_user_column_id_flex, 10);
1685 hr_utility.set_location('l_user_column_id_seg'||l_user_column_id_seg, 10);
1686 hr_utility.set_location('l_user_row_id_cat'||l_user_row_id_cat, 10);
1687 hr_utility.set_location('l_user_row_id_lev'||l_user_row_id_lev, 10);
1688 hr_utility.set_location('l_user_row_id_func'||l_user_row_id_func, 10);
1689 hr_utility.set_location('g_cat_flex'||g_cat_flex, 10);
1690 hr_utility.set_location('g_lev_flex'||g_lev_flex, 10);
1691 hr_utility.set_location('g_Func_flex'||g_Func_flex, 10);
1692 hr_utility.set_location('g_lev_segment'||g_lev_segment, 10);
1693 hr_utility.set_location('g_cat_segment'||g_cat_segment, 10);
1694 hr_utility.set_location('g_Func_segment'||g_Func_segment, 10);
1695 -- Verify the validity of the segments
1696 begin
1697
1698 l_temp := substr(g_lev_segment, 8);
1699 if substr(g_lev_segment, 1, 7) <> 'SEGMENT' or to_number(l_temp) < 1 or to_number(l_temp) > 30 then
1700 raise_application_error(-20003, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Segment.');
1701 end if;
1702
1703 l_temp := substr(g_cat_segment, 8);
1704 if substr(g_cat_segment, 1, 7) <> 'SEGMENT' or to_number(l_temp) < 1 or to_number(l_temp) > 30 then
1705 raise_application_error(-20003, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Segment.');
1706 end if;
1707
1708 exception
1709 when invalid_number then
1710 raise_application_error(-20003, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Segment.');
1711
1712 end;
1713
1714 exception
1715 when no_data_found then
1716 raise_application_error(-20001, 'The Occupational data does not exist in the User Table ZA_OCCUPATIONAL_TYPES.');
1717
1718 end cache_occupational_location;
1719
1720 -- This function returns the lookup_code from the user tables.
1721 function get_lookup_code
1722 (
1723 p_meaning in hr_lookups.meaning%type
1724 ) return varchar2 is
1725
1726 l_lookup_code hr_lookups.lookup_code%type;
1727
1728 begin
1729
1730 select distinct hl.lookup_code
1731 into l_lookup_code
1732 from hr_lookups hl
1733 where hl.lookup_type in ('ZA_EMP_EQ_OCCUPATIONAL_CAT', 'ZA_EMP_EQ_OCCUPATIONAL_LEV')
1734 and hl.meaning = p_meaning;
1735
1736 return l_lookup_code;
1737
1738 end get_lookup_code;
1739
1740 -- This function populates an entity's sex and race and category matches.
1741 procedure populate_ee_table
1742 (
1743 p_report_code in varchar2,
1744 p_report_date in per_all_assignments_f.effective_end_date%type,
1745 p_business_group_id in per_all_assignments_f.business_group_id%type,
1746 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
1747 ) is
1748
1749 l_counter number;
1750 l_reason varchar2(200);
1751
1752 begin
1753
1754 -- Note EQ1 is for the following 2 reports:
1755 -- 2. Occupational Categories (including employees with disabilities)
1756 -- 3. Occupational Categories (only employees with disabilities)
1757 if p_report_code = 'EQ1' then
1758
1759 -- Note: The date effective select on per_all_assignments_f is ok in this case, since an assignment
1760 -- record always exist at the same time as an employee record with status EMP
1761 insert into per_za_employment_equity
1762 (
1763 report_id,
1764 reporting_date,
1765 business_group_id,
1766 legal_entity_id,
1767 legal_entity,
1768 disability,
1769 employment_type,
1770 level_cat_code,
1771 level_cat,
1772 ma,
1773 mc,
1774 mi,
1775 mw,
1776 fa,
1777 fc,
1778 fi,
1779 fw,
1780 total
1781 )
1782 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1783 decode(papf.PER_INFORMATION3,null,null,
1784 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
1785 -to_char(p_report_date,'YYYYMMDD'))
1786 ,-1,null,'F'))
1787 ) report_code,
1788 p_report_date reporting_date,
1789 paaf.business_group_id,
1790 paei.aei_information7 legal_entity_id,
1791 haou.name legal_entity,
1792 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1793 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1794 -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1795 hl.lookup_code meaning_code,
1796 nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1797 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1798 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
1799 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
1800 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
1801 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
1802 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
1803 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
1804 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
1805 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
1806 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
1807 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
1808 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
1809 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
1810 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
1811 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
1812 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
1813 from hr_lookups hl,
1814 hr_lookups hl1,
1815 hr_lookups hl2,
1816 hr_all_organization_units haou,
1817 per_assignment_extra_info paei,
1818 per_all_assignments_f paaf,
1819 per_all_people_f papf
1820 where papf.business_group_id = p_business_group_id
1821 and p_report_date between papf.effective_start_date and papf.effective_end_date
1822 and papf.current_employee_flag = 'Y'
1823 and paaf.person_id = papf.person_id
1824 and paaf.primary_flag = 'Y'
1825 and p_report_date between paaf.effective_start_date and paaf.effective_end_date
1826 and paei.assignment_id = paaf.assignment_id
1827 and paei.information_type = 'ZA_SPECIFIC_INFO'
1828 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
1829 and paei.aei_information7 is not null
1830 and nvl(paei.aei_information6, 'N') <> 'Y'
1831 and haou.organization_id = paei.aei_information7
1832 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
1833 and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1834 AND hl.lookup_code <> '15' -- Not Applicable.
1835 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1836 AND hl1.lookup_code <> '15' -- Not Applicable.
1837 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1838 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1839 AND hl2.lookup_code <> '15' -- Not Applicable.
1840 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1841 group by paaf.business_group_id,
1842 paei.aei_information7,
1843 haou.name,
1844 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1845 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1846 -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1847 hl.lookup_code,
1848 nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1849 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1850 decode(papf.PER_INFORMATION3,null,null,
1851 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
1852 -to_char(p_report_date,'YYYYMMDD'))
1853 ,-1,null,'F'))
1854 );
1855
1856 commit;
1857
1858 -- Inserts non-associated occupational categories with zero values
1859 insert into per_za_employment_equity
1860 (
1861 report_id,
1862 reporting_date,
1863 business_group_id,
1864 legal_entity_id,
1865 legal_entity,
1866 disability,
1867 employment_type,
1868 level_cat_code,
1869 level_cat,
1870 MA,
1871 MC,
1872 MI,
1873 MW,
1874 FA,
1875 FC,
1876 FI,
1877 FW,
1878 total
1879 )
1880 select 'EQ1' report_id,
1881 p_report_date reporting_date,
1882 p_business_group_id business_group_id,
1883 haou.organization_id legal_entity_id,
1884 haou.name legal_entity,
1885 'Y' disability,
1886 'Permanent' employment_type,
1887 hl.lookup_code level_cat_code,
1888 hl.meaning level_cat,
1889 0 MA,
1890 0 MC,
1891 0 MI,
1892 0 MW,
1893 0 FA,
1894 0 FC,
1895 0 FI,
1896 0 FW,
1897 0 total
1898 from hr_lookups hl
1899 , hr_all_organization_units haou
1900 where not exists
1901 (
1902 select 'X'
1903 from per_za_employment_equity pzee
1904 where pzee.level_cat_code = hl.lookup_code
1905 and pzee.report_id = 'EQ1'
1906 and pzee.business_group_id = p_business_group_id --Bug 4872110
1907 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
1908 and pzee.disability = 'Y'
1909 and pzee.employment_type = 'Permanent'
1910 )
1911 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
1912 and haou.business_group_id = p_business_group_id --Bug 4872110
1913 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
1914
1915 -- Inseting 0 VALUES FOR FOREIGN nationals
1916 insert into per_za_employment_equity
1917 (
1918 report_id,
1919 reporting_date,
1920 business_group_id,
1921 legal_entity_id,
1922 legal_entity,
1923 disability,
1924 employment_type,
1925 level_cat_code,
1926 level_cat,
1927 MA,
1928 MC,
1929 MI,
1930 MW,
1931 FA,
1932 FC,
1933 FI,
1934 FW,
1935 total
1936 )
1937 select 'EQ1F' report_id,
1938 p_report_date reporting_date,
1939 p_business_group_id business_group_id,
1940 haou.organization_id legal_entity_id,
1941 haou.name legal_entity,
1942 'Y' disability,
1943 'Permanent' employment_type,
1944 hl.lookup_code level_cat_code,
1945 hl.meaning level_cat,
1946 0 MA,
1947 0 MC,
1948 0 MI,
1949 0 MW,
1950 0 FA,
1951 0 FC,
1952 0 FI,
1953 0 FW,
1954 0 total
1955 from hr_lookups hl
1956 , hr_all_organization_units haou
1957 where not exists
1958 (
1959 select 'X'
1960 from per_za_employment_equity pzee
1961 where pzee.level_cat_code = hl.lookup_code
1962 and pzee.report_id = 'EQ1F'
1963 and pzee.business_group_id = p_business_group_id --Bug 4872110
1964 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
1965 and pzee.disability = 'Y'
1966 and pzee.employment_type = 'Permanent'
1967 )
1968 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
1969 and haou.business_group_id = p_business_group_id --Bug 4872110
1970 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
1971
1972 commit;
1973
1974 -- Note EQ2 is for the following 2 reports:
1975 -- 4. Occupational Levels (including employees with disabilities)
1976 -- 5. Occupational Levels (only employees with disabilities)
1977 elsif p_report_code = 'EQ2' then
1978
1979 -- Populate with Occupational Level Totals
1980 insert into per_za_employment_equity
1981 (
1982 report_id,
1983 reporting_date,
1984 business_group_id,
1985 legal_entity_id,
1986 legal_entity,
1987 disability,
1988 employment_type,
1989 level_cat_code,
1990 level_cat,
1991 MA,
1992 MC,
1993 MI,
1994 MW,
1995 FA,
1996 FC,
1997 FI,
1998 FW,
1999 total
2000 )
2001 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2002 decode(papf.PER_INFORMATION3,null,null,
2003 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2004 -to_char(p_report_date,'YYYYMMDD'))
2005 ,-1,null,'F'))
2006 ) report_code,
2007 p_report_date reporting_date,
2008 paaf.business_group_id,
2009 paei.aei_information7 legal_entity_id,
2010 haou.name legal_entity,
2011 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2012 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2013 -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2014 hl.lookup_code meaning_code,
2015 nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2016 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2017 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2018 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2019 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
2020 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
2021 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
2022 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
2023 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
2024 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2025 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
2026 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
2027 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
2028 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2029 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
2030 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
2031 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2032 from hr_lookups hl,
2033 hr_lookups hl1,
2034 hr_lookups hl2,
2035 hr_all_organization_units haou,
2036 per_assignment_extra_info paei,
2037 per_all_assignments_f paaf,
2038 per_all_people_f papf
2039 where papf.business_group_id = p_business_group_id
2040 and p_report_date between papf.effective_start_date and papf.effective_end_date
2041 and papf.current_employee_flag = 'Y'
2042 and paaf.person_id = papf.person_id
2043 and paaf.primary_flag = 'Y'
2044 and p_report_date between paaf.effective_start_date and paaf.effective_end_date
2045 and paei.assignment_id = paaf.assignment_id
2046 and paei.information_type = 'ZA_SPECIFIC_INFO'
2047 and paei.aei_information7 = nvl(p_legal_entity_id,paei.aei_information7)
2048 and paei.aei_information7 is not null
2049 and nvl(paei.aei_information6, 'N') <> 'Y'
2050 and haou.organization_id = paei.aei_information7
2051 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2052 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2053 AND hl.lookup_code <> '15' -- Not Applicable.
2054 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2055 AND hl1.lookup_code <> '15' -- Operation / core function
2056 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2057 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2058 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2059 AND hl2.lookup_code <> '15' -- Not Applicable.
2060 group by paaf.business_group_id,
2061 paei.aei_information7,
2062 haou.name,
2063 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2064 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2065 -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2066 hl.lookup_code,
2067 nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2068 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2069 decode(papf.PER_INFORMATION3,null,null,
2070 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2071 -to_char(p_report_date,'YYYYMMDD'))
2072 ,-1,null,'F')));
2073
2074 commit;
2075
2076 -- Inserts non-associated occupational levels with zero values
2077 insert into per_za_employment_equity
2078 (
2079 report_id,
2080 reporting_date,
2081 business_group_id,
2082 legal_entity_id,
2083 legal_entity,
2084 disability,
2085 employment_type,
2086 level_cat_code,
2087 level_cat,
2088 MA,
2089 MC,
2090 MI,
2091 MW,
2092 FA,
2093 FC,
2094 FI,
2095 FW,
2096 total
2097 )
2098 select 'EQ2' report_id,
2099 p_report_date reporting_date,
2100 p_business_group_id business_group_id,
2101 haou.organization_id legal_entity_id,
2102 haou.name legal_entity,
2103 'Y' disability,
2104 'Permanent' employment_type,
2105 hl.lookup_code level_cat_code,
2106 hl.meaning level_cat,
2107 0 MA,
2108 0 MC,
2109 0 MI,
2110 0 MW,
2111 0 FA,
2112 0 FC,
2113 0 FI,
2114 0 FW,
2115 0 total
2116 from hr_lookups hl
2117 , hr_all_organization_units haou
2118 where not exists
2119 (
2120 select 'X'
2121 from per_za_employment_equity pzee
2122 where pzee.level_cat_code = hl.lookup_code
2123 and pzee.report_id = 'EQ2'
2124 and pzee.business_group_id = p_business_group_id --Bug 4872110
2125 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2126 and pzee.disability = 'Y'
2127 and pzee.employment_type = 'Permanent'
2128 )
2129 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2130 and haou.business_group_id = p_business_group_id --Bug 4872110
2131 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2132
2133
2134 -- inserting 0 values for the Foreign Nationals
2135 insert into per_za_employment_equity
2136 (
2137 report_id,
2138 reporting_date,
2139 business_group_id,
2140 legal_entity_id,
2141 legal_entity,
2142 disability,
2143 employment_type,
2144 level_cat_code,
2145 level_cat,
2146 MA,
2147 MC,
2148 MI,
2149 MW,
2150 FA,
2151 FC,
2152 FI,
2153 FW,
2154 total
2155 )
2156 select 'EQ2F' report_id,
2157 p_report_date reporting_date,
2158 p_business_group_id business_group_id,
2159 haou.organization_id legal_entity_id,
2160 haou.name legal_entity,
2161 'Y' disability,
2162 'Permanent' employment_type,
2163 hl.lookup_code level_cat_code,
2164 hl.meaning level_cat,
2165 0 MA,
2166 0 MC,
2167 0 MI,
2168 0 MW,
2169 0 FA,
2170 0 FC,
2171 0 FI,
2172 0 FW,
2173 0 total
2174 from hr_lookups hl
2175 , hr_all_organization_units haou
2176 where not exists
2177 (
2178 select 'X'
2179 from per_za_employment_equity pzee
2180 where pzee.level_cat_code = hl.lookup_code
2181 and pzee.report_id = 'EQ2F'
2182 and pzee.business_group_id = p_business_group_id --Bug 4872110
2183 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2184 and pzee.disability = 'Y'
2185 and pzee.employment_type = 'Permanent'
2186 )
2187 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2188 and haou.business_group_id = p_business_group_id --Bug 4872110
2189 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2190
2191
2192 commit;
2193
2194 -- For employment equity enhancement
2195 elsif p_report_code = 'EQ3' then
2196
2197 -- Populate with New Hires
2198 insert into per_za_employment_equity
2199 (
2200 report_id,
2201 reporting_date,
2202 business_group_id,
2203 legal_entity_id,
2204 legal_entity,
2205 disability,
2206 employment_type,
2207 level_cat_code,
2208 level_cat,
2209 MA,
2210 MC,
2211 MI,
2212 MW,
2213 FA,
2214 FC,
2215 FI,
2216 FW,
2217 total
2218 )
2219 select tpa.report_code,
2220 tpa.reporting_date,
2221 tpa.business_group_id,
2222 tpa.legal_entity_id,
2223 tpa.legal_entity,
2224 tpa.disability,
2225 tpa.employment_type,
2226 tpa.meaning_code,
2227 tpa.occupational_level,
2228 sum(tpa.male_african) MA,
2229 sum(tpa.male_coloured) MC,
2230 sum(tpa.male_indian) MI,
2231 sum(tpa.male_white) MW,
2232 sum(tpa.female_african) FA,
2233 sum(tpa.female_coloured) FC,
2234 sum(tpa.female_indian) FI,
2235 sum(tpa.female_white) FW,
2236 sum(tpa.total) total
2237 from
2238 (
2239 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2240 decode(papf.PER_INFORMATION3,null,null,
2241 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2242 -to_char(p_report_date,'YYYYMMDD'))
2243 ,-1,null,'F'))) report_code,
2244 p_report_date reporting_date,
2245 paaf.business_group_id,
2246 paei.aei_information7 legal_entity_id,
2247 haou.name legal_entity,
2248 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2249 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2250 -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2251 hl.lookup_code meaning_code,
2252 nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2253 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2254 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2255 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2256 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
2257 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
2258 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
2259 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
2260 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
2261 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2262 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
2263 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
2264 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
2265 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2266 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
2267 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
2268 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2269 from hr_lookups hl,
2270 hr_lookups hl1,
2271 hr_lookups hl2,
2272 hr_all_organization_units haou,
2273 per_assignment_extra_info paei,
2274 per_all_assignments_f paaf,
2275 per_all_people_f papf
2276 where papf.business_group_id = p_business_group_id
2277 and papf.current_employee_flag = 'Y'
2278 and p_report_date between papf.effective_start_date and papf.effective_end_date
2279 and paaf.person_id = papf.person_id
2280 and paaf.primary_flag = 'Y'
2281 and p_report_date between paaf.effective_start_date and paaf.effective_end_date
2282 and paei.assignment_id = paaf.assignment_id
2283 and paei.information_type = 'ZA_SPECIFIC_INFO'
2284 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
2285 and paei.aei_information7 is not null
2286 and nvl(paei.aei_information6, 'N') <> 'Y'
2287 and haou.organization_id = paei.aei_information7
2288 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2289 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2290 AND hl.lookup_code <> '15' -- Not Applicable.
2291 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2292 AND hl1.lookup_code = '1' -- Operation / core function
2293 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2294 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2295 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2296 AND hl2.lookup_code <> '15' -- Not Applicable.
2297 group by paaf.business_group_id,
2298 paei.aei_information7,
2299 haou.name,
2300 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2301 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2302 -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2303 hl.lookup_code,
2304 nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2305 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2306 decode(papf.PER_INFORMATION3,null,null,
2307 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2308 -to_char(p_report_date,'YYYYMMDD'))
2309 ,-1,null,'F')))
2310 ) tpa
2311 group by tpa.report_code,
2312 tpa.reporting_date,
2313 tpa.business_group_id,
2314 tpa.legal_entity_id,
2315 tpa.legal_entity,
2316 tpa.disability,
2317 tpa.employment_type,
2318 tpa.meaning_code,
2319 tpa.occupational_level;
2320
2321 commit;
2322
2323 -- Inserts non-associated occupational levels with zero values
2324 insert into per_za_employment_equity
2325 (
2326 report_id,
2327 reporting_date,
2328 business_group_id,
2329 legal_entity_id,
2330 legal_entity,
2331 disability,
2332 employment_type,
2333 level_cat_code,
2334 level_cat,
2335 MA,
2336 MC,
2337 MI,
2338 MW,
2339 FA,
2340 FC,
2341 FI,
2342 FW,
2343 total
2344 )
2345 select 'EQ3' report_id,
2346 p_report_date reporting_date,
2347 p_business_group_id business_group_id,
2348 haou.organization_id legal_entity_id,
2349 haou.name legal_entity,
2350 'Y' disability,
2351 'Permanent' employment_type,
2352 hl.lookup_code level_cat_code,
2353 hl.meaning level_cat,
2354 0 MA,
2355 0 MC,
2356 0 MI,
2357 0 MW,
2358 0 FA,
2359 0 FC,
2360 0 FI,
2361 0 FW,
2362 0 total
2363 from hr_lookups hl
2364 , hr_all_organization_units haou
2365 where not exists
2366 (
2367 select 'X'
2368 from per_za_employment_equity pzee
2369 where pzee.level_cat_code = hl.lookup_code
2370 and pzee.report_id = 'EQ3'
2371 and pzee.business_group_id = p_business_group_id --Bug 4872110
2372 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2373 and pzee.disability = 'Y'
2374 and pzee.employment_type = 'Permanent'
2375 )
2376 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2377 and haou.business_group_id = p_business_group_id --Bug 4872110
2378 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2379
2380 -- Inserts non-associated occupational levels with zero values
2381 insert into per_za_employment_equity
2382 (
2383 report_id,
2384 reporting_date,
2385 business_group_id,
2386 legal_entity_id,
2387 legal_entity,
2388 disability,
2389 employment_type,
2390 level_cat_code,
2391 level_cat,
2392 MA,
2393 MC,
2394 MI,
2395 MW,
2396 FA,
2397 FC,
2398 FI,
2399 FW,
2400 total
2401 )
2402 select 'EQ3F' report_id,
2403 p_report_date reporting_date,
2404 p_business_group_id business_group_id,
2405 haou.organization_id legal_entity_id,
2406 haou.name legal_entity,
2407 'Y' disability,
2408 'Permanent' employment_type,
2409 hl.lookup_code level_cat_code,
2410 hl.meaning level_cat,
2411 0 MA,
2412 0 MC,
2413 0 MI,
2414 0 MW,
2415 0 FA,
2416 0 FC,
2417 0 FI,
2418 0 FW,
2419 0 total
2420 from hr_lookups hl
2421 , hr_all_organization_units haou
2422 where not exists
2423 (
2424 select 'X'
2425 from per_za_employment_equity pzee
2426 where pzee.level_cat_code = hl.lookup_code
2427 and pzee.report_id = 'EQ3F'
2428 and pzee.business_group_id = p_business_group_id --Bug 4872110
2429 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2430 and pzee.disability = 'Y'
2431 and pzee.employment_type = 'Permanent'
2432 )
2433 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2434 and haou.business_group_id = p_business_group_id --Bug 4872110
2435 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2436
2437 commit;
2438
2439 -- Note EQ4 is for the following report:
2440 -- 2.3.1. Operational / core Functiona (report the total number of new recruits into each occupational level during
2441 -- the twelve months preceding this report)
2442 elsif p_report_code = 'EQ4' then
2443
2444 -- Populate with New Hires
2445 insert into per_za_employment_equity
2446 (
2447 report_id,
2448 reporting_date,
2449 business_group_id,
2450 legal_entity_id,
2451 legal_entity,
2452 disability,
2453 employment_type,
2454 level_cat_code,
2455 level_cat,
2456 MA,
2457 MC,
2458 MI,
2459 MW,
2460 FA,
2461 FC,
2462 FI,
2463 FW,
2464 total
2465 )
2466 select tpa.report_code,
2467 tpa.reporting_date,
2468 tpa.business_group_id,
2469 tpa.legal_entity_id,
2470 tpa.legal_entity,
2471 tpa.disability,
2472 tpa.employment_type,
2473 tpa.meaning_code,
2474 tpa.occupational_level,
2475 sum(tpa.male_african) MA,
2476 sum(tpa.male_coloured) MC,
2477 sum(tpa.male_indian) MI,
2478 sum(tpa.male_white) MW,
2479 sum(tpa.female_african) FA,
2480 sum(tpa.female_coloured) FC,
2481 sum(tpa.female_indian) FI,
2482 sum(tpa.female_white) FW,
2483 sum(tpa.total) total
2484 from
2485 (
2486 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2487 decode(papf.PER_INFORMATION3,null,null,
2488 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2489 -to_char(p_report_date,'YYYYMMDD'))
2490 ,-1,null,'F'))) report_code,
2491 p_report_date reporting_date,
2492 paaf.business_group_id,
2493 paei.aei_information7 legal_entity_id,
2494 haou.name legal_entity,
2495 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2496 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2497 -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2498 hl.lookup_code meaning_code,
2499 nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2500 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2501 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2502 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2503 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
2504 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
2505 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
2506 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
2507 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
2508 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2509 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
2510 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
2511 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
2512 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2513 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
2514 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
2515 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2516 from hr_lookups hl,
2517 hr_lookups hl1,
2518 hr_lookups hl2,
2519 hr_all_organization_units haou,
2520 per_assignment_extra_info paei,
2521 per_all_assignments_f paaf,
2522 per_all_people_f papf
2523 where papf.business_group_id = p_business_group_id
2524 and papf.current_employee_flag = 'Y'
2525 and p_report_date between papf.effective_start_date and papf.effective_end_date
2526 and paaf.person_id = papf.person_id
2527 and paaf.primary_flag = 'Y'
2528 and p_report_date between paaf.effective_start_date and paaf.effective_end_date
2529 and paei.assignment_id = paaf.assignment_id
2530 and paei.information_type = 'ZA_SPECIFIC_INFO'
2531 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
2532 and paei.aei_information7 is not null
2533 and nvl(paei.aei_information6, 'N') <> 'Y'
2534 and haou.organization_id = paei.aei_information7
2535 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2536 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2537 AND hl.lookup_code <> '15' -- Not Applicable.
2538 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2539 AND hl1.lookup_code = '2' -- Support function
2540 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2541 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2542 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2543 AND hl2.lookup_code <> '15' -- Not Applicable.
2544 group by paaf.business_group_id,
2545 paei.aei_information7,
2546 haou.name,
2547 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2548 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2549 -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2550 hl.lookup_code,
2551 nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2552 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2553 decode(papf.PER_INFORMATION3,null,null,
2554 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2555 -to_char(p_report_date,'YYYYMMDD'))
2556 ,-1,null,'F')))
2557 ) tpa
2558 group by tpa.report_code,
2559 tpa.reporting_date,
2560 tpa.business_group_id,
2561 tpa.legal_entity_id,
2562 tpa.legal_entity,
2563 tpa.disability,
2564 tpa.employment_type,
2565 tpa.meaning_code,
2566 tpa.occupational_level;
2567
2568 commit;
2569
2570 -- Inserts non-associated occupational levels with zero values
2571 insert into per_za_employment_equity
2572 (
2573 report_id,
2574 reporting_date,
2575 business_group_id,
2576 legal_entity_id,
2577 legal_entity,
2578 disability,
2579 employment_type,
2580 level_cat_code,
2581 level_cat,
2582 MA,
2583 MC,
2584 MI,
2585 MW,
2586 FA,
2587 FC,
2588 FI,
2589 FW,
2590 total
2591 )
2592 select 'EQ4' report_id,
2593 p_report_date reporting_date,
2594 p_business_group_id business_group_id,
2595 haou.organization_id legal_entity_id,
2596 haou.name legal_entity,
2597 'Y' disability,
2598 'Permanent' employment_type,
2599 hl.lookup_code level_cat_code,
2600 hl.meaning level_cat,
2601 0 MA,
2602 0 MC,
2603 0 MI,
2604 0 MW,
2605 0 FA,
2606 0 FC,
2607 0 FI,
2608 0 FW,
2609 0 total
2610 from hr_lookups hl
2611 , hr_all_organization_units haou
2612 where not exists
2613 (
2614 select 'X'
2615 from per_za_employment_equity pzee
2616 where pzee.level_cat_code = hl.lookup_code
2617 and pzee.report_id = 'EQ4'
2618 and pzee.business_group_id = p_business_group_id --Bug 4872110
2619 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2620 and pzee.disability = 'Y'
2621 and pzee.employment_type = 'Permanent'
2622 )
2623 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2624 and haou.business_group_id = p_business_group_id --Bug 4872110
2625 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2626
2627 -- Inserts non-associated occupational levels with zero values
2628 insert into per_za_employment_equity
2629 (
2630 report_id,
2631 reporting_date,
2632 business_group_id,
2633 legal_entity_id,
2634 legal_entity,
2635 disability,
2636 employment_type,
2637 level_cat_code,
2638 level_cat,
2639 MA,
2640 MC,
2641 MI,
2642 MW,
2643 FA,
2644 FC,
2645 FI,
2646 FW,
2647 total
2648 )
2649 select 'EQ4F' report_id,
2650 p_report_date reporting_date,
2651 p_business_group_id business_group_id,
2652 haou.organization_id legal_entity_id,
2653 haou.name legal_entity,
2654 'Y' disability,
2655 'Permanent' employment_type,
2656 hl.lookup_code level_cat_code,
2657 hl.meaning level_cat,
2658 0 MA,
2659 0 MC,
2660 0 MI,
2661 0 MW,
2662 0 FA,
2663 0 FC,
2664 0 FI,
2665 0 FW,
2666 0 total
2667 from hr_lookups hl
2668 , hr_all_organization_units haou
2669 where not exists
2670 (
2671 select 'X'
2672 from per_za_employment_equity pzee
2673 where pzee.level_cat_code = hl.lookup_code
2674 and pzee.report_id = 'EQ4F'
2675 and pzee.business_group_id = p_business_group_id --Bug 4872110
2676 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2677 and pzee.disability = 'Y'
2678 and pzee.employment_type = 'Permanent'
2679 )
2680 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2681 and haou.business_group_id = p_business_group_id --Bug 4872110
2682 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2683
2684 commit;
2685
2686 -- End for Emplyment Equity enhancement
2687
2688
2689 -- Note EQ5 is for the following report:
2690 -- 6. Recruitment (report the total number of new recruits into each occupational level during
2691 -- the twelve months preceding this report)
2692 elsif p_report_code = 'EQ5' then
2693
2694 -- Populate with New Hires
2695 insert into per_za_employment_equity
2696 (
2697 report_id,
2698 reporting_date,
2699 business_group_id,
2700 legal_entity_id,
2701 legal_entity,
2702 disability,
2703 employment_type,
2704 level_cat_code,
2705 level_cat,
2706 MA,
2707 MC,
2708 MI,
2709 MW,
2710 FA,
2711 FC,
2712 FI,
2713 FW,
2714 total
2715 )
2716 select tpa.report_code,
2717 tpa.reporting_date,
2718 tpa.business_group_id,
2719 tpa.legal_entity_id,
2720 tpa.legal_entity,
2721 tpa.disability,
2722 tpa.employment_type,
2723 tpa.meaning_code,
2724 tpa.occupational_level,
2725 sum(tpa.male_african) MA,
2726 sum(tpa.male_coloured) MC,
2727 sum(tpa.male_indian) MI,
2728 sum(tpa.male_white) MW,
2729 sum(tpa.female_african) FA,
2730 sum(tpa.female_coloured) FC,
2731 sum(tpa.female_indian) FI,
2732 sum(tpa.female_white) FW,
2733 sum(tpa.total) total
2734 from
2735 (
2736 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2737 decode(papf.PER_INFORMATION3,null,null,
2738 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2739 -to_char(p_report_date,'YYYYMMDD'))
2740 ,-1,null,'F'))) report_code,
2741 p_report_date reporting_date,
2742 paaf.business_group_id,
2743 paei.aei_information7 legal_entity_id,
2744 haou.name legal_entity,
2745 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2746 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2747 -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2748 hl.lookup_code meaning_code,
2749 nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2750 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2751 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2752 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2753 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
2754 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
2755 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
2756 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
2757 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
2758 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2759 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
2760 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
2761 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
2762 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2763 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
2764 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
2765 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2766 from hr_lookups hl,
2767 hr_lookups hl1,
2768 hr_lookups hl2,
2769 hr_all_organization_units haou,
2770 per_assignment_extra_info paei,
2771 per_all_assignments_f paaf,
2772 per_periods_of_service ppos,
2773 per_all_people_f papf
2774 where papf.business_group_id = p_business_group_id
2775 and papf.current_employee_flag = 'Y'
2776 and ppos.person_id = papf.person_id
2777 and ppos.date_start between add_months(p_report_date, -12) + 1 and p_report_date
2778 and papf.effective_start_date = ppos.date_start
2779 and paaf.person_id = papf.person_id
2780 and paaf.primary_flag = 'Y'
2781 and paaf.effective_start_date = ppos.date_start
2782 and paei.assignment_id = paaf.assignment_id
2783 and paei.information_type = 'ZA_SPECIFIC_INFO'
2784 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
2785 and paei.aei_information7 is not null
2786 and nvl(paei.aei_information6, 'N') <> 'Y'
2787 and haou.organization_id = paei.aei_information7
2788 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2789 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2790 AND hl.lookup_code <> '15' -- Not Applicable.
2791 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2792 and hl1.lookup_code <> '15' -- Not Applicable.
2793 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2794 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2795 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2796 AND hl2.lookup_code <> '15' -- Not Applicable.
2797 group by paaf.business_group_id,
2798 paei.aei_information7,
2799 haou.name,
2800 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2801 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2802 -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2803 hl.lookup_code,
2804 nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2805 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2806 decode(papf.PER_INFORMATION3,null,null,
2807 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2808 -to_char(p_report_date,'YYYYMMDD'))
2809 ,-1,null,'F')))
2810 ) tpa
2811 group by tpa.report_code,
2812 tpa.reporting_date,
2813 tpa.business_group_id,
2814 tpa.legal_entity_id,
2815 tpa.legal_entity,
2816 tpa.disability,
2817 tpa.employment_type,
2818 tpa.meaning_code,
2819 tpa.occupational_level;
2820
2821 commit;
2822
2823 -- Inserts non-associated occupational levels with zero values
2824 insert into per_za_employment_equity
2825 (
2826 report_id,
2827 reporting_date,
2828 business_group_id,
2829 legal_entity_id,
2830 legal_entity,
2831 disability,
2832 employment_type,
2833 level_cat_code,
2834 level_cat,
2835 MA,
2836 MC,
2837 MI,
2838 MW,
2839 FA,
2840 FC,
2841 FI,
2842 FW,
2843 total
2844 )
2845 select 'EQ5' report_id,
2846 p_report_date reporting_date,
2847 p_business_group_id business_group_id,
2848 haou.organization_id legal_entity_id,
2849 haou.name legal_entity,
2850 'Y' disability,
2851 'Permanent' employment_type,
2852 hl.lookup_code level_cat_code,
2853 hl.meaning level_cat,
2854 0 MA,
2855 0 MC,
2856 0 MI,
2857 0 MW,
2858 0 FA,
2859 0 FC,
2860 0 FI,
2861 0 FW,
2862 0 total
2863 from hr_lookups hl
2864 , hr_all_organization_units haou
2865 where not exists
2866 (
2867 select 'X'
2868 from per_za_employment_equity pzee
2869 where pzee.level_cat_code = hl.lookup_code
2870 and pzee.report_id = 'EQ5'
2871 and pzee.business_group_id = p_business_group_id --Bug 4872110
2872 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2873 and pzee.disability = 'Y'
2874 and pzee.employment_type = 'Permanent'
2875 )
2876 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2877 and haou.business_group_id = p_business_group_id --Bug 4872110
2878 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2879
2880 -- Inserts non-associated occupational levels with zero values
2881 insert into per_za_employment_equity
2882 (
2883 report_id,
2884 reporting_date,
2885 business_group_id,
2886 legal_entity_id,
2887 legal_entity,
2888 disability,
2889 employment_type,
2890 level_cat_code,
2891 level_cat,
2892 MA,
2893 MC,
2894 MI,
2895 MW,
2896 FA,
2897 FC,
2898 FI,
2899 FW,
2900 total
2901 )
2902 select 'EQ5F' report_id,
2903 p_report_date reporting_date,
2904 p_business_group_id business_group_id,
2905 haou.organization_id legal_entity_id,
2906 haou.name legal_entity,
2907 'Y' disability,
2908 'Permanent' employment_type,
2909 hl.lookup_code level_cat_code,
2910 hl.meaning level_cat,
2911 0 MA,
2912 0 MC,
2913 0 MI,
2914 0 MW,
2915 0 FA,
2916 0 FC,
2917 0 FI,
2918 0 FW,
2919 0 total
2920 from hr_lookups hl
2921 , hr_all_organization_units haou
2922 where not exists
2923 (
2924 select 'X'
2925 from per_za_employment_equity pzee
2926 where pzee.level_cat_code = hl.lookup_code
2927 and pzee.report_id = 'EQ5F'
2928 and pzee.business_group_id = p_business_group_id --Bug 4872110
2929 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
2930 and pzee.disability = 'Y'
2931 and pzee.employment_type = 'Permanent'
2932 )
2933 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2934 and haou.business_group_id = p_business_group_id --Bug 4872110
2935 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2936
2937 commit;
2938
2939 -- Note EQ6 is for the following report:
2940 -- 7. Promotion (report the total number of promotions into each occupational level during
2941 -- the twelve months preceding this report)
2942 elsif p_report_code = 'EQ6' then
2943
2944 -- Populate with Promotions
2945 insert into per_za_employment_equity
2946 (
2947 report_id,
2948 reporting_date,
2949 business_group_id,
2950 legal_entity_id,
2951 legal_entity,
2952 disability,
2953 employment_type,
2954 level_cat_code,
2955 level_cat,
2956 MA,
2957 MC,
2958 MI,
2959 MW,
2960 FA,
2961 FC,
2962 FI,
2963 FW,
2964 total
2965 )
2966 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2967 decode(papf.PER_INFORMATION3,null,null,
2968 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2969 - to_char(p_report_date,'YYYYMMDD'))
2970 ,-1,null,'F'))) report_code,
2971 p_report_date reporting_date,
2972 paaf.business_group_id,
2973 paei.aei_information7 legal_entity_id,
2974 haou.name legal_entity,
2975 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2976 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2977 -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
2978 hl.lookup_code lookup_code,
2979 nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2980 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2981 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2982 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2983 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
2984 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
2985 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
2986 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
2987 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
2988 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2989 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
2990 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
2991 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
2992 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
2993 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
2994 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
2995 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
2996 from hr_lookups hl,
2997 hr_lookups hl1,
2998 hr_lookups hl2,
2999 hr_all_organization_units haou,
3000 per_assignment_extra_info paei,
3001 per_all_assignments_f paaf,
3002 per_periods_of_service ppos,
3003 per_all_people_f papf
3004 where papf.business_group_id = p_business_group_id
3005 and papf.current_employee_flag = 'Y'
3006 and ppos.person_id = papf.person_id
3007 and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) > add_months(p_report_date, -12) + 1
3008 and ppos.date_start < p_report_date
3009 and papf.effective_start_date = ppos.date_start
3010 and paaf.person_id = papf.person_id
3011 and paaf.primary_flag = 'Y'
3012 and paaf.effective_start_date between ppos.date_start and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
3013 and paaf.effective_start_date > add_months(p_report_date, -12) + 1
3014 and paaf.effective_start_date <= p_report_date
3015 and paei.assignment_id = paaf.assignment_id
3016 and paei.information_type = 'ZA_SPECIFIC_INFO'
3017 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
3018 and paei.aei_information7 is not null
3019 and nvl(paei.aei_information6, 'N') <> 'Y'
3020 and haou.organization_id = paei.aei_information7
3021 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3022 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3023 AND hl.lookup_code <> '15' -- Not Applicable.
3024 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3025 and hl1.lookup_code <> '15' -- Not Applicable.
3026 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3027 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3028 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3029 AND hl2.lookup_code <> '15' -- Not Applicable.
3030 and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3031 any
3032 (
3033 select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3034 from per_all_assignments_f paaf1
3035 where paaf1.person_id = papf.person_id
3036 and paaf1.primary_flag = 'Y'
3037 and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null
3038 and paaf1.effective_end_date + 1 = paaf.effective_start_date
3039 )
3040 group by paaf.business_group_id,
3041 paei.aei_information7,
3042 haou.name,
3043 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3044 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3045 -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3046 hl.lookup_code,
3047 nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3048 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3049 decode(papf.PER_INFORMATION3,null,null,
3050 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3051 -to_char(p_report_date,'YYYYMMDD'))
3052 ,-1,null,'F'))) ;
3053
3054 commit;
3055
3056 -- Inserts non-associated occupational levels with zero values
3057 insert into per_za_employment_equity
3058 (
3059 report_id,
3060 reporting_date,
3061 business_group_id,
3062 legal_entity_id,
3063 legal_entity,
3064 disability,
3065 employment_type,
3066 level_cat_code,
3067 level_cat,
3068 MA,
3069 MC,
3070 MI,
3071 MW,
3072 FA,
3073 FC,
3074 FI,
3075 FW,
3076 total
3077 )
3078 select 'EQ6' report_id,
3079 p_report_date reporting_date,
3080 p_business_group_id business_group_id,
3081 haou.organization_id legal_entity_id,
3082 haou.name legal_entity,
3083 'Y' disability,
3084 'Permanent' employment_type,
3085 hl.lookup_code level_cat_code,
3086 hl.meaning level_cat,
3087 0 MA,
3088 0 MC,
3089 0 MI,
3090 0 MW,
3091 0 FA,
3092 0 FC,
3093 0 FI,
3094 0 FW,
3095 0 total
3096 from hr_lookups hl
3097 , hr_all_organization_units haou
3098 where not exists
3099 (
3100 select 'X'
3101 from per_za_employment_equity pzee
3102 where pzee.level_cat_code = hl.lookup_code
3103 and pzee.report_id = 'EQ6'
3104 and pzee.business_group_id = p_business_group_id --Bug 4872110
3105 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
3106 and pzee.disability = 'Y'
3107 and pzee.employment_type = 'Permanent'
3108 )
3109 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3110 and haou.business_group_id = p_business_group_id --Bug 4872110
3111 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3112
3113 -- Inserts non-associated occupational levels with zero values
3114 insert into per_za_employment_equity
3115 (
3116 report_id,
3117 reporting_date,
3118 business_group_id,
3119 legal_entity_id,
3120 legal_entity,
3121 disability,
3122 employment_type,
3123 level_cat_code,
3124 level_cat,
3125 MA,
3126 MC,
3127 MI,
3128 MW,
3129 FA,
3130 FC,
3131 FI,
3132 FW,
3133 total
3134 )
3135 select 'EQ6F' report_id,
3136 p_report_date reporting_date,
3137 p_business_group_id business_group_id,
3138 haou.organization_id legal_entity_id,
3139 haou.name legal_entity,
3140 'Y' disability,
3141 'Permanent' employment_type,
3142 hl.lookup_code level_cat_code,
3143 hl.meaning level_cat,
3144 0 MA,
3145 0 MC,
3146 0 MI,
3147 0 MW,
3148 0 FA,
3149 0 FC,
3150 0 FI,
3151 0 FW,
3152 0 total
3153 from hr_lookups hl
3154 , hr_all_organization_units haou
3155 where not exists
3156 (
3157 select 'X'
3158 from per_za_employment_equity pzee
3159 where pzee.level_cat_code = hl.lookup_code
3160 and pzee.report_id = 'EQ6F'
3161 and pzee.business_group_id = p_business_group_id --Bug 4872110
3162 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
3163 and pzee.disability = 'Y'
3164 and pzee.employment_type = 'Permanent'
3165 )
3166 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3167 and haou.business_group_id = p_business_group_id --Bug 4872110
3168 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3169
3170
3171 commit;
3172
3173 -- Note EQ5 is for the following report:
3174 -- 8.1 Termination (report the total number of terminations in each occupational level during
3175 -- the twelve months preceding this report)
3176 elsif p_report_code = 'EQ7' then
3177
3178 -- Populate with Terminations
3179 insert into per_za_employment_equity
3180 (
3181 report_id,
3182 reporting_date,
3183 business_group_id,
3184 legal_entity_id,
3185 legal_entity,
3186 disability,
3187 employment_type,
3188 level_cat_code,
3189 level_cat,
3190 MA,
3191 MC,
3192 MI,
3193 MW,
3194 FA,
3195 FC,
3196 FI,
3197 FW,
3198 total
3199 )
3200 select tpa.report_code,
3201 tpa.reporting_date,
3202 tpa.business_group_id,
3203 tpa.legal_entity_id,
3204 tpa.legal_entity,
3205 tpa.disability,
3206 tpa.employment_type,
3207 tpa.meaning_code,
3208 tpa.occupational_level,
3209 sum(tpa.male_african) MA,
3210 sum(tpa.male_coloured) MC,
3211 sum(tpa.male_indian) MI,
3212 sum(tpa.male_white) MW,
3213 sum(tpa.female_african) FA,
3214 sum(tpa.female_coloured) FC,
3215 sum(tpa.female_indian) FI,
3216 sum(tpa.female_white) FW,
3217 sum(tpa.total) total
3218 from
3219 (
3220 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3221 decode(papf.PER_INFORMATION3,null,null,
3222 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3223 -to_char(p_report_date,'YYYYMMDD'))
3224 ,-1,null,'F'))
3225 ) report_code,
3226 p_report_date reporting_date,
3227 paaf.business_group_id,
3228 paei.aei_information7 legal_entity_id,
3229 haou.name legal_entity,
3230 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3231 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3232 -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3233 hl.lookup_code meaning_code,
3234 nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3235 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3236 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3237 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3238 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
3239 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
3240 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
3241 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
3242 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
3243 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
3244 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
3245 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
3246 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
3247 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
3248 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
3249 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
3250 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
3251 from hr_lookups hl,
3252 hr_lookups hl1,
3253 hr_lookups hl2,
3254 hr_all_organization_units haou,
3255 per_assignment_extra_info paei,
3256 per_all_assignments_f paaf,
3257 per_periods_of_service ppos,
3258 per_all_people_f papf
3259 where papf.business_group_id = p_business_group_id
3260 and papf.current_employee_flag = 'Y'
3261 and ppos.person_id = papf.person_id
3262 and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) between add_months(p_report_date, -12) + 1 and p_report_date
3263 and papf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
3264 and paaf.person_id = papf.person_id
3265 and paaf.primary_flag = 'Y'
3266 and paaf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
3267 and paei.assignment_id = paaf.assignment_id
3268 and paei.information_type = 'ZA_SPECIFIC_INFO'
3269 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
3270 and paei.aei_information7 is not null
3271 and nvl(paei.aei_information6, 'N') <> 'Y'
3272 and haou.organization_id = paei.aei_information7
3273 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3274 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3275 AND hl.lookup_code <> '15' -- Not Applicable.
3276 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3277 and hl1.lookup_code <> '15' -- Not Applicable
3278 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3279 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3280 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3281 AND hl2.lookup_code <> '15' -- Not Applicable.
3282 group by paaf.business_group_id,
3283 paei.aei_information7,
3284 haou.name,
3285 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3286 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3287 -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3288 hl.lookup_code,
3289 nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3290 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3291 decode(papf.PER_INFORMATION3,null,null,
3292 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3293 -to_char(p_report_date,'YYYYMMDD'))
3294 ,-1,null,'F'))
3295 )
3296 ) tpa
3297 group by tpa.report_code,
3298 tpa.reporting_date,
3299 tpa.business_group_id,
3300 tpa.legal_entity_id,
3301 tpa.legal_entity,
3302 tpa.disability,
3303 tpa.employment_type,
3304 tpa.meaning_code,
3305 tpa.occupational_level;
3306
3307 commit;
3308
3309 -- Inserts non-associated occupational levels with zero values
3310 insert into per_za_employment_equity
3311 (
3312 report_id,
3313 reporting_date,
3314 business_group_id,
3315 legal_entity_id,
3316 legal_entity,
3317 disability,
3318 employment_type,
3319 level_cat_code,
3320 level_cat,
3321 MA,
3322 MC,
3323 MI,
3324 MW,
3325 FA,
3326 FC,
3327 FI,
3328 FW,
3329 total
3330 )
3331 select 'EQ7' report_id,
3332 p_report_date reporting_date,
3333 p_business_group_id business_group_id,
3334 haou.organization_id legal_entity_id,
3335 haou.name legal_entity,
3336 'Y' disability,
3337 'Permanent' employment_type,
3338 hl.lookup_code level_cat_code,
3339 hl.meaning level_cat,
3340 0 MA,
3341 0 MC,
3342 0 MI,
3343 0 MW,
3344 0 FA,
3345 0 FC,
3346 0 FI,
3347 0 FW,
3348 0 total
3349 from hr_lookups hl
3350 , hr_all_organization_units haou
3351 where not exists
3352 (
3353 select 'X'
3354 from per_za_employment_equity pzee
3355 where pzee.level_cat_code = hl.lookup_code
3356 and pzee.report_id = 'EQ7'
3357 and pzee.business_group_id = p_business_group_id --Bug 4872110
3358 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
3359 and pzee.disability = 'Y'
3360 and pzee.employment_type = 'Permanent'
3361 )
3362 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3363 and haou.business_group_id = p_business_group_id --Bug 4872110
3364 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3365
3366 insert into per_za_employment_equity
3367 (
3368 report_id,
3369 reporting_date,
3370 business_group_id,
3371 legal_entity_id,
3372 legal_entity,
3373 disability,
3374 employment_type,
3375 level_cat_code,
3376 level_cat,
3377 MA,
3378 MC,
3379 MI,
3380 MW,
3381 FA,
3382 FC,
3383 FI,
3384 FW,
3385 total
3386 )
3387 select 'EQ7F' report_id,
3388 p_report_date reporting_date,
3389 p_business_group_id business_group_id,
3390 haou.organization_id legal_entity_id,
3391 haou.name legal_entity,
3392 'Y' disability,
3393 'Permanent' employment_type,
3394 hl.lookup_code level_cat_code,
3395 hl.meaning level_cat,
3396 0 MA,
3397 0 MC,
3398 0 MI,
3399 0 MW,
3400 0 FA,
3401 0 FC,
3402 0 FI,
3403 0 FW,
3404 0 total
3405 from hr_lookups hl
3406 , hr_all_organization_units haou
3407 where not exists
3408 (
3409 select 'X'
3410 from per_za_employment_equity pzee
3411 where pzee.level_cat_code = hl.lookup_code
3412 and pzee.report_id = 'EQ7F'
3413 and pzee.business_group_id = p_business_group_id --Bug 4872110
3414 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
3415 and pzee.disability = 'Y'
3416 and pzee.employment_type = 'Permanent'
3417 )
3418 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3419 and haou.business_group_id = p_business_group_id --Bug 4872110
3420 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3421
3422 commit;
3423
3424 -- Note EQ6 is for the following report:
3425 -- 8.2 Termination Categories (report the total number of terminations in each termination
3426 -- category during the twelve months preceding this report)
3427 elsif p_report_code = 'EQ8' then
3428
3429 -- Populate with Termination Reason totals
3430 insert into per_za_employment_equity
3431 (
3432 report_id,
3433 reporting_date,
3434 business_group_id,
3435 legal_entity_id,
3436 legal_entity,
3437 disability,
3438 employment_type,
3439 level_cat_code,
3440 level_cat,
3441 MA,
3442 MC,
3443 MI,
3444 MW,
3445 FA,
3446 FC,
3447 FI,
3448 FW,
3449 total
3450 )
3451 select tpa.report_code,
3452 tpa.reporting_date,
3453 tpa.business_group_id,
3454 tpa.legal_entity_id,
3455 tpa.legal_entity,
3456 tpa.disability,
3457 tpa.employment_type,
3458 decode
3459 (
3460 tpa.termination_reason,
3461 'Resignation', 1,
3462 'Non-Renewal of Contract', 2,
3463 'Dismissal - Operational Requirements', 3,
3464 'Dismissal - Misconduct', 4,
3465 'Dismissal - Incapacity', 5,
3466 'Other', 6,
3467 null
3468 ) meaning_code,
3469 decode
3470 (
3471 tpa.termination_reason,
3472 'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
3473 tpa.termination_reason
3474 ),
3475 sum(tpa.male_african) MA,
3476 sum(tpa.male_coloured) MC,
3477 sum(tpa.male_indian) MI,
3478 sum(tpa.male_white) MW,
3479 sum(tpa.female_african) FA,
3480 sum(tpa.female_coloured) FC,
3481 sum(tpa.female_indian) FI,
3482 sum(tpa.female_white) FW,
3483 sum(tpa.total) total
3484 from
3485 (
3486 select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3487 decode(papf.PER_INFORMATION3,null,null,
3488 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3489 -to_char(p_report_date,'YYYYMMDD'))
3490 ,-1,null,'F'))
3491 ) report_code,
3492 p_report_date reporting_date,
3493 paaf.business_group_id,
3494 paei.aei_information7 legal_entity_id,
3495 haou.name legal_entity,
3496 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3497 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3498 -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3499 ppos.leaving_reason meaning_code,
3500 nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3501 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3502 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3503 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3504 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
3505 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
3506 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
3507 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
3508 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
3509 sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
3510 sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
3511 sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
3512 sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
3513 sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
3514 sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
3515 sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
3516 sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
3517 from hr_lookups hl,
3518 hr_lookups hl1,
3519 hr_lookups hl2,
3520 hr_all_organization_units haou,
3521 per_assignment_extra_info paei,
3522 per_all_assignments_f paaf,
3523 per_periods_of_service ppos,
3524 per_all_people_f papf
3525 where papf.business_group_id = p_business_group_id
3526 and papf.current_employee_flag = 'Y'
3527 and ppos.person_id = papf.person_id
3528 and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) between add_months(p_report_date, -12) + 1 and p_report_date
3529 and papf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
3530 and paaf.person_id = papf.person_id
3531 and paaf.primary_flag = 'Y'
3532 and paaf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
3533 and paei.assignment_id = paaf.assignment_id
3534 and paei.information_type = 'ZA_SPECIFIC_INFO'
3535 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
3536 and paei.aei_information7 is not null
3537 and nvl(paei.aei_information6, 'N') <> 'Y'
3538 and haou.organization_id = paei.aei_information7
3539 and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3540 and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3541 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3542 and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3543 and hl.lookup_code <> '15' -- Not Applicable
3544 and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3545 and hl1.lookup_code <> '15' -- Not Applicable
3546 and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3547 and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3548 and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3549 and hl2.lookup_code <> '15' -- Not Applicable
3550 group by paaf.business_group_id,
3551 paei.aei_information7,
3552 haou.name,
3553 decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3554 nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3555 -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3556 ppos.leaving_reason,
3557 nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3558 p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3559 decode(papf.PER_INFORMATION3,null,null,
3560 decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3561 -to_char(p_report_date,'YYYYMMDD'))
3562 ,-1,null,'F'))
3563 )
3564 ) tpa
3565 group by tpa.report_code,
3566 tpa.reporting_date,
3567 tpa.business_group_id,
3568 tpa.legal_entity_id,
3569 tpa.legal_entity,
3570 tpa.disability,
3571 tpa.employment_type,
3572 tpa.meaning_code,
3573 tpa.termination_reason;
3574
3575 commit;
3576
3577 -- Insert zeroes for any Termination Categories that weren't used
3578 for l_counter in 1..6 loop
3579
3580 -- The hard coded names of the legislative Termination Categories (not stored anywhere)
3581 if l_counter = 1 then
3582 l_reason := 'Resignation';
3583 elsif l_counter = 2 then
3584 l_reason := 'Non-Renewal of Contract';
3585 elsif l_counter = 3 then
3586 l_reason := 'Dismissal - Operational Requirements';
3587 elsif l_counter = 4 then
3588 l_reason := 'Dismissal - Misconduct';
3589 elsif l_counter = 5 then
3590 l_reason := 'Dismissal - Incapacity';
3591 else
3592 l_reason := 'Other';
3593 end if;
3594
3595 insert into per_za_employment_equity
3596 (
3597 report_id,
3598 reporting_date,
3599 business_group_id,
3600 legal_entity_id,
3601 legal_entity,
3602 disability,
3603 employment_type,
3604 level_cat_code,
3605 level_cat,
3606 MA,
3607 MC,
3608 MI,
3609 MW,
3610 FA,
3611 FC,
3612 FI,
3613 FW,
3614 total
3615 )
3616 select 'EQ8' report_id,
3617 p_report_date reporting_date,
3618 p_business_group_id business_group_id,
3619 haou.organization_id legal_entity_id,
3620 haou.name legal_entity,
3621 'Y' disability,
3622 'Permanent' employment_type,
3623 decode
3624 (
3625 l_reason,
3626 'Resignation', 1,
3627 'Non-Renewal of Contract', 2,
3628 'Dismissal - Operational Requirements', 3,
3629 'Dismissal - Misconduct', 4,
3630 'Dismissal - Incapacity', 5,
3631 'Other', 6,
3632 null
3633 ) level_cat_code,
3634 decode
3635 (
3636 l_reason,
3637 'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
3638 l_reason
3639 ) level_cat,
3640 0 MA,
3641 0 MC,
3642 0 MI,
3643 0 MW,
3644 0 FA,
3645 0 FC,
3646 0 FI,
3647 0 FW,
3648 0 total
3649 from hr_all_organization_units haou
3650 where not exists
3651 (
3652 select 'X'
3653 from per_za_employment_equity pzee
3654 where pzee.level_cat = l_reason
3655 and pzee.report_id = 'EQ8'
3656 and pzee.business_group_id = p_business_group_id --Bug 4872110
3657 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
3658 and pzee.disability = 'Y'
3659 and pzee.employment_type = 'Permanent'
3660 )
3661 and haou.business_group_id = p_business_group_id --Bug 4872110
3662 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3663
3664
3665 insert into per_za_employment_equity
3666 (
3667 report_id,
3668 reporting_date,
3669 business_group_id,
3670 legal_entity_id,
3671 legal_entity,
3672 disability,
3673 employment_type,
3674 level_cat_code,
3675 level_cat,
3676 MA,
3677 MC,
3678 MI,
3679 MW,
3680 FA,
3681 FC,
3682 FI,
3683 FW,
3684 total
3685 )
3686 select 'EQ8F' report_id,
3687 p_report_date reporting_date,
3688 p_business_group_id business_group_id,
3689 haou.organization_id legal_entity_id,
3690 haou.name legal_entity,
3691 'Y' disability,
3692 'Permanent' employment_type,
3693 decode
3694 (
3695 l_reason,
3696 'Resignation', 1,
3697 'Non-Renewal of Contract', 2,
3698 'Dismissal - Operational Requirements', 3,
3699 'Dismissal - Misconduct', 4,
3700 'Dismissal - Incapacity', 5,
3701 'Other', 6,
3702 null
3703 ) level_cat_code,
3704 decode
3705 (
3706 l_reason,
3707 'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
3708 l_reason
3709 ) level_cat,
3710 0 MA,
3711 0 MC,
3712 0 MI,
3713 0 MW,
3714 0 FA,
3715 0 FC,
3716 0 FI,
3717 0 FW,
3718 0 total
3719 from hr_all_organization_units haou
3720 where not exists
3721 (
3722 select 'X'
3723 from per_za_employment_equity pzee
3724 where pzee.level_cat = l_reason
3725 and pzee.report_id = 'EQ8F'
3726 and pzee.business_group_id = p_business_group_id --Bug 4872110
3727 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
3728 and pzee.disability = 'Y'
3729 and pzee.employment_type = 'Permanent'
3730 )
3731 and haou.business_group_id = p_business_group_id --Bug 4872110
3732 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3733
3734 end loop;
3735
3736 commit;
3737
3738 end if;
3739
3740 end populate_ee_table;
3741
3742
3743 function get_functional_type
3744 (
3745 p_report_date in per_all_assignments_f.effective_end_date%type,
3746 p_assignment_id in per_all_assignments_f.assignment_id%type,
3747 p_job_id in per_all_assignments_f.job_id%type,
3748 p_grade_id in per_all_assignments_f.grade_id%type,
3749 p_position_id in per_all_assignments_f.position_id%type,
3750 p_business_group_id in per_all_assignments_f.business_group_id%type
3751 ) return VARCHAR2 is
3752
3753 begin
3754 -- Check whether we have cached the location of Occupational data
3755 if g_func_flex is null then
3756
3757 cache_occupational_location(p_report_date, p_business_group_id);
3758
3759 end if;
3760
3761 -- Check whether the current assignment's value is cached already
3762 /* if p_report_date = g_cat_report_date
3763 and p_assignment_id = g_cat_asg_id then
3764
3765 return g_f_type_name;
3766
3767 else
3768
3769 g_cat_report_date := p_report_date;
3770 g_cat_asg_id := p_assignment_id;
3771 */
3772 g_f_type_name := get_functional_data
3773 (
3774 p_flex => g_Func_flex,
3775 p_segment => g_Func_segment,
3776 p_job_id => p_job_id,
3777 p_grade_id => p_grade_id,
3778 p_position_id => p_position_id
3779 );
3780
3781 return g_f_type_name;
3782
3783 -- end if;
3784
3785 END get_functional_type;
3786
3787 -- This function retrieves the functional data via dynamic sql from the appropriate flexfield segment
3788 function get_functional_data
3789 (
3790 p_flex in varchar2,
3791 p_segment in varchar2,
3792 p_job_id in per_all_assignments_f.job_id%type,
3793 p_grade_id in per_all_assignments_f.grade_id%type,
3794 p_position_id in per_all_assignments_f.position_id%type
3795 ) return varchar2 is
3796
3797 l_sql varchar2(32767);
3798 l_name hr_lookups.meaning%type;
3799
3800 begin
3801
3802 if p_flex = 'Job' then
3803
3804 begin
3805
3806 if p_job_id is not null then
3807
3808 l_sql := 'select hl.meaning from hr_lookups hl, per_job_definitions pjd, per_jobs pj where pj.job_id = '
3809 || to_char(p_job_id)
3810 || ' and pjd.job_definition_id = pj.job_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EE_FUNCTION_TYPE'
3811 || ''' and hl.lookup_code = pjd.' || p_segment;
3812
3813 execute immediate l_sql into l_name;
3814
3815 else
3816
3817 l_name := null;
3818
3819 end if;
3820
3821 exception
3822 when no_data_found then
3823 l_name := null;
3824
3825 end;
3826
3827 elsif p_flex = 'Grade' then
3828
3829 begin
3830
3831 if p_grade_id is not null then
3832
3833 l_sql := 'select hl.meaning from hr_lookups hl, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
3834 || to_char(p_grade_id)
3835 || ' and pgd.grade_definition_id = pg.grade_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EE_FUNCTION_TYPE'
3836 || ''' and hl.lookup_code = pgd.' || p_segment;
3837
3838 execute immediate l_sql into l_name;
3839
3840 else
3841
3842 l_name := null;
3843
3844 end if;
3845
3846 exception
3847 when no_data_found then
3848 l_name := null;
3849
3850 end;
3851
3852 elsif p_flex = 'Position' then
3853
3854 begin
3855
3856 if p_position_id is not null then
3857
3858 l_sql := 'select hl.meaning from hr_lookups hl, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
3859 || to_char(p_position_id)
3860 || ' and ppd.position_definition_id = pap.position_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EE_FUNCTION_TYPE'
3861 || ''' and hl.lookup_code = ppd.' || p_segment;
3862
3863 execute immediate l_sql into l_name;
3864
3865 else
3866
3867 l_name := null;
3868
3869 end if;
3870
3871 exception
3872 when no_data_found then
3873 l_name := null;
3874
3875 end;
3876
3877 else
3878
3879 raise_application_error(-20002, 'The Functional Type in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Flexfield.');
3880
3881 end if;
3882
3883 return l_name;
3884
3885 end get_functional_data;
3886
3887 procedure populate_ee_table_EEWF
3888 (
3889 p_report_date in per_all_assignments_f.effective_end_date%type,
3890 p_business_group_id in per_all_assignments_f.business_group_id%type,
3891 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
3892 ) is
3893
3894 begin
3895 DELETE FROM per_za_employment_equity
3896 Where REPORT_ID IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8',
3897 'EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F'
3898 );
3899
3900 populate_ee_table (
3901 p_report_code =>'EQ1'
3902 , p_report_date =>p_report_date
3903 , p_business_group_id =>p_business_group_id
3904 , p_legal_entity_id =>p_legal_entity_id
3905 );
3906
3907 populate_ee_table (
3908 p_report_code =>'EQ2'
3909 , p_report_date =>p_report_date
3910 , p_business_group_id =>p_business_group_id
3911 , p_legal_entity_id =>p_legal_entity_id
3912 );
3913
3914 populate_ee_table (
3915 p_report_code =>'EQ3'
3916 , p_report_date =>p_report_date
3917 , p_business_group_id =>p_business_group_id
3918 , p_legal_entity_id =>p_legal_entity_id
3919 );
3920
3921 populate_ee_table (
3922 p_report_code =>'EQ4'
3923 , p_report_date =>p_report_date
3924 , p_business_group_id =>p_business_group_id
3925 , p_legal_entity_id =>p_legal_entity_id
3926 );
3927
3928 populate_ee_table (
3929 p_report_code =>'EQ5'
3930 , p_report_date =>p_report_date
3931 , p_business_group_id =>p_business_group_id
3932 , p_legal_entity_id =>p_legal_entity_id
3933 );
3934
3935 populate_ee_table (
3936 p_report_code =>'EQ6'
3937 , p_report_date =>p_report_date
3938 , p_business_group_id =>p_business_group_id
3939 , p_legal_entity_id =>p_legal_entity_id
3940 );
3941
3942 populate_ee_table (
3943 p_report_code =>'EQ7'
3944 , p_report_date =>p_report_date
3945 , p_business_group_id =>p_business_group_id
3946 , p_legal_entity_id =>p_legal_entity_id
3947 );
3948
3949 populate_ee_table (
3950 p_report_code =>'EQ8'
3951 , p_report_date =>p_report_date
3952 , p_business_group_id =>p_business_group_id
3953 , p_legal_entity_id =>p_legal_entity_id
3954 );
3955
3956 -- Inserts non-associated occupational categories with zero values
3957 insert into per_za_employment_equity
3958 (
3959 report_id,
3960 reporting_date,
3961 business_group_id,
3962 legal_entity_id,
3963 legal_entity,
3964 disability,
3965 employment_type,
3966 level_cat_code,
3967 level_cat,
3968 MA,
3969 MC,
3970 MI,
3971 MW,
3972 FA,
3973 FC,
3974 FI,
3975 FW,
3976 total
3977 )
3978 select substr(report_id,1,3),
3979 reporting_date,
3980 business_group_id,
3981 legal_entity_id,
3982 legal_entity,
3983 disability,
3984 employment_type,
3985 level_cat_code,
3986 level_cat,
3987 0,
3988 0,
3989 0,
3990 0,
3991 0,
3992 0,
3993 0,
3994 0,
3995 0
3996 from per_za_employment_equity pzee1
3997 Where pzee1.business_group_id = p_business_group_id
3998 AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
3999 AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
4000 AND not exists
4001 (
4002 select 'X'
4003 from per_za_employment_equity pzee
4004 where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
4005 AND pzee.legal_entity_id = pzee1.legal_entity_id
4006 AND pzee.report_id ||'F' = pzee1.report_id
4007 AND pzee1.level_cat_code = pzee.level_cat_code
4008 AND pzee1.level_cat = pzee.level_cat
4009 and nvl(pzee.disability,'X') = nvl(pzee1.disability,'X')
4010 and pzee.employment_type = pzee1.employment_type
4011 );
4012
4013
4014 -- Inserts non-associated occupational categories with zero values
4015 insert into per_za_employment_equity
4016 (
4017 report_id,
4018 reporting_date,
4019 business_group_id,
4020 legal_entity_id,
4021 legal_entity,
4022 disability,
4023 employment_type,
4024 level_cat_code,
4025 level_cat,
4026 MA,
4027 MC,
4028 MI,
4029 MW,
4030 FA,
4031 FC,
4032 FI,
4033 FW,
4034 total
4035 )
4036 select report_id||'F' report_id,
4037 reporting_date,
4038 business_group_id,
4039 legal_entity_id,
4040 legal_entity,
4041 disability,
4042 employment_type,
4043 level_cat_code,
4044 level_cat,
4045 0,
4046 0,
4047 0,
4048 0,
4049 0,
4050 0,
4051 0,
4052 0,
4053 0
4054 from per_za_employment_equity pzee1
4055 Where pzee1.business_group_id = p_business_group_id
4056 AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
4057 AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
4058 AND not exists
4059 (
4060 select 'X'
4061 from per_za_employment_equity pzee
4062 where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
4063 AND pzee.legal_entity_id = pzee1.legal_entity_id
4064 AND pzee1.report_id ||'F' = pzee.report_id
4065 AND pzee1.level_cat_code = pzee.level_cat_code
4066 AND pzee1.level_cat = pzee.level_cat
4067 and nvl(pzee.disability,'X') = nvl(pzee1.disability,'X')
4068 and pzee.employment_type = pzee1.employment_type
4069 );
4070
4071 commit;
4072
4073
4074 End populate_ee_table_EEWF;
4075
4076 -- Procedure to insert data into global tables.
4077 -- used in Employment Equity Encome Differential report
4078 --
4079 PROCEDURE ins_g_Enc_Diff_table(p_mi_inc IN number
4080 , p_mc_inc IN number
4081 , p_ma_inc IN number
4082 , p_mw_inc IN number
4083 , p_fa_inc IN number
4084 , p_fc_inc IN number
4085 , p_fi_inc IN number
4086 , p_fw_inc IN number
4087 , p_total_inc IN number
4088 , p_ma IN number
4089 , p_mc IN number
4090 , p_mi IN number
4091 , p_mw IN number
4092 , p_fa IN number
4093 , p_fc IN number
4094 , p_fi IN number
4095 , p_fw IN number
4096 , p_total IN number
4097 , p_cat_index IN number
4098 , p_lev_index IN number
4099 , p_legal_entity_id IN hr_all_organization_units.organization_id%type
4100 , p_occupational_level IN hr_lookups.meaning%type
4101 , p_occupational_category IN hr_lookups.meaning%type
4102 , p_occupational_level_id IN hr_lookups.lookup_code%type
4103 , p_occupational_category_id IN hr_lookups.lookup_code%type
4104 ) is
4105
4106 begin
4107
4108 IF g_cat_Enc_Diff_table.EXISTS(p_cat_index) then
4109 g_cat_Enc_Diff_table(p_cat_index).mi_inc := g_cat_Enc_Diff_table(p_cat_index).mi_inc + p_mi_inc ;
4110 g_cat_Enc_Diff_table(p_cat_index).mc_inc := g_cat_Enc_Diff_table(p_cat_index).mc_inc + p_mc_inc ;
4111 g_cat_Enc_Diff_table(p_cat_index).ma_inc := g_cat_Enc_Diff_table(p_cat_index).ma_inc + p_ma_inc ;
4112 g_cat_Enc_Diff_table(p_cat_index).mw_inc := g_cat_Enc_Diff_table(p_cat_index).mw_inc + p_mw_inc ;
4113 g_cat_Enc_Diff_table(p_cat_index).fa_inc := g_cat_Enc_Diff_table(p_cat_index).fa_inc + p_fa_inc ;
4114 g_cat_Enc_Diff_table(p_cat_index).fc_inc := g_cat_Enc_Diff_table(p_cat_index).fc_inc + p_fc_inc ;
4115 g_cat_Enc_Diff_table(p_cat_index).fi_inc := g_cat_Enc_Diff_table(p_cat_index).fi_inc + p_fi_inc ;
4116 g_cat_Enc_Diff_table(p_cat_index).fw_inc := g_cat_Enc_Diff_table(p_cat_index).fw_inc + p_fw_inc ;
4117 g_cat_Enc_Diff_table(p_cat_index).total_inc := g_cat_Enc_Diff_table(p_cat_index).total_inc + p_total_inc;
4118
4119 g_cat_Enc_Diff_table(p_cat_index).mi := g_cat_Enc_Diff_table(p_cat_index).mi + p_mi ;
4120 g_cat_Enc_Diff_table(p_cat_index).mc := g_cat_Enc_Diff_table(p_cat_index).mc + p_mc ;
4121 g_cat_Enc_Diff_table(p_cat_index).ma := g_cat_Enc_Diff_table(p_cat_index).ma + p_ma ;
4122 g_cat_Enc_Diff_table(p_cat_index).mw := g_cat_Enc_Diff_table(p_cat_index).mw + p_mw ;
4123 g_cat_Enc_Diff_table(p_cat_index).fa := g_cat_Enc_Diff_table(p_cat_index).fa + p_fa ;
4124 g_cat_Enc_Diff_table(p_cat_index).fc := g_cat_Enc_Diff_table(p_cat_index).fc + p_fc ;
4125 g_cat_Enc_Diff_table(p_cat_index).fi := g_cat_Enc_Diff_table(p_cat_index).fi + p_fi ;
4126 g_cat_Enc_Diff_table(p_cat_index).fw := g_cat_Enc_Diff_table(p_cat_index).fw + p_fw ;
4127 g_cat_Enc_Diff_table(p_cat_index).total := g_cat_Enc_Diff_table(p_cat_index).total + p_total;
4128
4129 else
4130 g_cat_Enc_Diff_table(p_cat_index).mi_inc := p_mi_inc ;
4131 g_cat_Enc_Diff_table(p_cat_index).mc_inc := p_mc_inc ;
4132 g_cat_Enc_Diff_table(p_cat_index).ma_inc := p_ma_inc ;
4133 g_cat_Enc_Diff_table(p_cat_index).mw_inc := p_mw_inc ;
4134 g_cat_Enc_Diff_table(p_cat_index).fa_inc := p_fa_inc ;
4135 g_cat_Enc_Diff_table(p_cat_index).fc_inc := p_fc_inc ;
4136 g_cat_Enc_Diff_table(p_cat_index).fi_inc := p_fi_inc ;
4137 g_cat_Enc_Diff_table(p_cat_index).fw_inc := p_fw_inc ;
4138 g_cat_Enc_Diff_table(p_cat_index).total_inc := p_total_inc;
4139
4140 g_cat_Enc_Diff_table(p_cat_index).mi := p_mi ;
4141 g_cat_Enc_Diff_table(p_cat_index).mc := p_mc ;
4142 g_cat_Enc_Diff_table(p_cat_index).ma := p_ma ;
4143 g_cat_Enc_Diff_table(p_cat_index).mw := p_mw ;
4144 g_cat_Enc_Diff_table(p_cat_index).fa := p_fa ;
4145 g_cat_Enc_Diff_table(p_cat_index).fc := p_fc ;
4146 g_cat_Enc_Diff_table(p_cat_index).fi := p_fi ;
4147 g_cat_Enc_Diff_table(p_cat_index).fw := p_fw ;
4148 g_cat_Enc_Diff_table(p_cat_index).total := p_total;
4149 g_cat_Enc_Diff_table(p_cat_index).legal_entity_id := p_legal_entity_id;
4150 g_cat_Enc_Diff_table(p_cat_index).occupational_code := p_occupational_category;
4151 g_cat_Enc_Diff_table(p_cat_index).occupational_code_id := p_occupational_category_id;
4152 END if;
4153 IF g_lev_Enc_Diff_table.EXISTS(p_lev_index) then
4154 g_lev_Enc_Diff_table(p_lev_index).mi_inc := g_lev_Enc_Diff_table(p_lev_index).mi_inc + p_mi_inc ;
4155 g_lev_Enc_Diff_table(p_lev_index).mc_inc := g_lev_Enc_Diff_table(p_lev_index).mc_inc + p_mc_inc ;
4156 g_lev_Enc_Diff_table(p_lev_index).ma_inc := g_lev_Enc_Diff_table(p_lev_index).ma_inc + p_ma_inc ;
4157 g_lev_Enc_Diff_table(p_lev_index).mw_inc := g_lev_Enc_Diff_table(p_lev_index).mw_inc + p_mw_inc ;
4158 g_lev_Enc_Diff_table(p_lev_index).fa_inc := g_lev_Enc_Diff_table(p_lev_index).fa_inc + p_fa_inc ;
4159 g_lev_Enc_Diff_table(p_lev_index).fc_inc := g_lev_Enc_Diff_table(p_lev_index).fc_inc + p_fc_inc ;
4160 g_lev_Enc_Diff_table(p_lev_index).fi_inc := g_lev_Enc_Diff_table(p_lev_index).fi_inc + p_fi_inc ;
4161 g_lev_Enc_Diff_table(p_lev_index).fw_inc := g_lev_Enc_Diff_table(p_lev_index).fw_inc + p_fw_inc ;
4162 g_lev_Enc_Diff_table(p_lev_index).total_inc := g_lev_Enc_Diff_table(p_lev_index).total_inc + p_total_inc;
4163
4164 g_lev_Enc_Diff_table(p_lev_index).ma := g_lev_Enc_Diff_table(p_lev_index).ma + p_ma ;
4165 g_lev_Enc_Diff_table(p_lev_index).mc := g_lev_Enc_Diff_table(p_lev_index).mc + p_mc ;
4166 g_lev_Enc_Diff_table(p_lev_index).mi := g_lev_Enc_Diff_table(p_lev_index).mi + p_mi ;
4167 g_lev_Enc_Diff_table(p_lev_index).mw := g_lev_Enc_Diff_table(p_lev_index).mw + p_mw ;
4168 g_lev_Enc_Diff_table(p_lev_index).fa := g_lev_Enc_Diff_table(p_lev_index).fa + p_fa ;
4169 g_lev_Enc_Diff_table(p_lev_index).fc := g_lev_Enc_Diff_table(p_lev_index).fc + p_fc ;
4170 g_lev_Enc_Diff_table(p_lev_index).fi := g_lev_Enc_Diff_table(p_lev_index).fi + p_fi ;
4171 g_lev_Enc_Diff_table(p_lev_index).fw := g_lev_Enc_Diff_table(p_lev_index).fw + p_fw ;
4172 g_lev_Enc_Diff_table(p_lev_index).total := g_lev_Enc_Diff_table(p_lev_index).total + p_total;
4173 else
4174 g_lev_Enc_Diff_table(p_lev_index).mi_inc := p_mi_inc ;
4175 g_lev_Enc_Diff_table(p_lev_index).mc_inc := p_mc_inc ;
4176 g_lev_Enc_Diff_table(p_lev_index).ma_inc := p_ma_inc ;
4177 g_lev_Enc_Diff_table(p_lev_index).mw_inc := p_mw_inc ;
4178 g_lev_Enc_Diff_table(p_lev_index).fa_inc := p_fa_inc ;
4179 g_lev_Enc_Diff_table(p_lev_index).fc_inc := p_fc_inc ;
4180 g_lev_Enc_Diff_table(p_lev_index).fi_inc := p_fi_inc ;
4181 g_lev_Enc_Diff_table(p_lev_index).fw_inc := p_fw_inc ;
4182 g_lev_Enc_Diff_table(p_lev_index).total_inc := p_total_inc;
4183
4184 g_lev_Enc_Diff_table(p_lev_index).ma := p_ma ;
4185 g_lev_Enc_Diff_table(p_lev_index).mc := p_mc ;
4186 g_lev_Enc_Diff_table(p_lev_index).mi := p_mi ;
4187 g_lev_Enc_Diff_table(p_lev_index).mw := p_mw ;
4188 g_lev_Enc_Diff_table(p_lev_index).fa := p_fa ;
4189 g_lev_Enc_Diff_table(p_lev_index).fc := p_fc ;
4190 g_lev_Enc_Diff_table(p_lev_index).fi := p_fi ;
4191 g_lev_Enc_Diff_table(p_lev_index).fw := p_fw ;
4192 g_lev_Enc_Diff_table(p_lev_index).total := p_total;
4193 g_lev_Enc_Diff_table(p_lev_index).legal_entity_id := p_legal_entity_id;
4194 g_lev_Enc_Diff_table(p_lev_index).occupational_code := p_occupational_level;
4195 g_lev_Enc_Diff_table(p_lev_index).occupational_code_id := p_occupational_level_id;
4196 End if;
4197
4198 END ins_g_Enc_Diff_table;
4199
4200 -- Procedure is used to sort the employee data
4201 -- and ready to inset into global tables
4202
4203 Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
4204 , p_occupational_level IN hr_lookups.meaning%type
4205 , p_occupational_category IN hr_lookups.meaning%type
4206 , p_race IN per_all_people_f.per_information4%type
4207 , p_sex IN per_all_people_f.sex%type
4208 , p_income IN number
4209 , p_occupational_level_id IN hr_lookups.lookup_code%type
4210 , p_occupational_category_id IN hr_lookups.lookup_code%type
4211 ) is
4212
4213 l_cat_index pls_integer ;
4214 l_lev_index pls_integer ;
4215 begin
4216
4217 begin
4218
4219 l_cat_index := p_legal_entity_id *100 + nvl(p_occupational_category_id,0);
4220 l_lev_index := p_legal_entity_id *100 + nvl(p_occupational_level_id,0);
4221 hr_utility.set_location('l_cat_index ' || l_cat_index, 25);
4222 hr_utility.set_location('l_lev_index ' || l_lev_index, 25);
4223
4224 exception
4225 when others then
4226 raise_application_error(-20006, 'The lookup code in the ZA_EMP_EQ_OCCUPATIONAL_LEV and ZA_EMP_EQ_OCCUPATIONAL_CAT lookups must be numeric.');
4227
4228 end;
4229
4230 CASE p_sex||p_race
4231 WHEN 'M01' THEN --male Indian (MI)
4232 ins_g_Enc_Diff_table(p_mi_inc => p_income
4233 , p_mc_inc => 0
4234 , p_ma_inc => 0
4235 , p_mw_inc => 0
4236 , p_fa_inc => 0
4237 , p_fc_inc => 0
4238 , p_fi_inc => 0
4239 , p_fw_inc => 0
4240 , p_total_inc => p_income
4241 , p_ma => 0
4242 , p_mc => 0
4243 , p_mi => 1
4244 , p_mw => 0
4245 , p_fa => 0
4246 , p_fc => 0
4247 , p_fi => 0
4248 , p_fw => 0
4249 , p_total => 1
4250 , p_cat_index => l_cat_index
4251 , p_lev_index => l_lev_index
4252 , p_legal_entity_id => p_legal_entity_id
4253 , p_occupational_level => p_occupational_level
4254 , p_occupational_category => p_occupational_category
4255 , p_occupational_level_id => p_occupational_level_id
4256 , p_occupational_category_id => p_occupational_category_id
4257 );
4258 WHEN 'M02' THEN --male African
4259 ins_g_Enc_Diff_table(p_mi_inc => 0
4260 , p_mc_inc => 0
4261 , p_ma_inc => p_income
4262 , p_mw_inc => 0
4263 , p_fa_inc => 0
4264 , p_fc_inc => 0
4265 , p_fi_inc => 0
4266 , p_fw_inc => 0
4267 , p_total_inc => p_income
4268 , p_ma => 1
4269 , p_mc => 0
4270 , p_mi => 0
4271 , p_mw => 0
4272 , p_fa => 0
4273 , p_fc => 0
4274 , p_fi => 0
4275 , p_fw => 0
4276 , p_total => 1
4277 , p_cat_index => l_cat_index
4278 , p_lev_index => l_lev_index
4279 , p_legal_entity_id => p_legal_entity_id
4280 , p_occupational_level => p_occupational_level
4281 , p_occupational_category => p_occupational_category
4282 , p_occupational_level_id => p_occupational_level_id
4283 , p_occupational_category_id => p_occupational_category_id
4284 );
4285 WHEN 'M03' THEN --male Coloured
4286 ins_g_Enc_Diff_table(p_mi_inc => 0
4287 , p_mc_inc => p_income
4288 , p_ma_inc => 0
4289 , p_mw_inc => 0
4290 , p_fa_inc => 0
4291 , p_fc_inc => 0
4292 , p_fi_inc => 0
4293 , p_fw_inc => 0
4294 , p_total_inc => p_income
4295 , p_ma => 0
4296 , p_mc => 1
4297 , p_mi => 0
4298 , p_mw => 0
4299 , p_fa => 0
4300 , p_fc => 0
4301 , p_fi => 0
4302 , p_fw => 0
4303 , p_total => 1
4304 , p_cat_index => l_cat_index
4305 , p_lev_index => l_lev_index
4306 , p_legal_entity_id => p_legal_entity_id
4307 , p_occupational_level => p_occupational_level
4308 , p_occupational_category => p_occupational_category
4309 , p_occupational_level_id => p_occupational_level_id
4310 , p_occupational_category_id => p_occupational_category_id
4311 );
4312 WHEN 'M04' THEN --male White
4313 ins_g_Enc_Diff_table(p_mi_inc => 0
4314 , p_mc_inc => 0
4315 , p_ma_inc => 0
4316 , p_mw_inc => p_income
4317 , p_fa_inc => 0
4318 , p_fc_inc => 0
4319 , p_fi_inc => 0
4320 , p_fw_inc => 0
4321 , p_total_inc => p_income
4322 , p_ma => 0
4323 , p_mc => 0
4324 , p_mi => 0
4325 , p_mw => 1
4326 , p_fa => 0
4327 , p_fc => 0
4328 , p_fi => 0
4329 , p_fw => 0
4330 , p_total => 1
4331 , p_cat_index => l_cat_index
4332 , p_lev_index => l_lev_index
4333 , p_legal_entity_id => p_legal_entity_id
4334 , p_occupational_level => p_occupational_level
4335 , p_occupational_category => p_occupational_category
4336 , p_occupational_level_id => p_occupational_level_id
4337 , p_occupational_category_id => p_occupational_category_id
4338 );
4339 WHEN 'MZA01' THEN --male Chinese (To be reported as African)
4340 ins_g_Enc_Diff_table(p_mi_inc => 0
4341 , p_mc_inc => 0
4342 , p_ma_inc => p_income
4343 , p_mw_inc => 0
4344 , p_fa_inc => 0
4345 , p_fc_inc => 0
4346 , p_fi_inc => 0
4347 , p_fw_inc => 0
4348 , p_total_inc => p_income
4349 , p_ma => 1
4350 , p_mc => 0
4351 , p_mi => 0
4352 , p_mw => 0
4353 , p_fa => 0
4354 , p_fc => 0
4355 , p_fi => 0
4356 , p_fw => 0
4357 , p_total => 1
4358 , p_cat_index => l_cat_index
4359 , p_lev_index => l_lev_index
4360 , p_legal_entity_id => p_legal_entity_id
4361 , p_occupational_level => p_occupational_level
4362 , p_occupational_category => p_occupational_category
4363 , p_occupational_level_id => p_occupational_level_id
4364 , p_occupational_category_id => p_occupational_category_id
4365 );
4366
4367 WHEN 'F01' THEN --female Indian
4368 ins_g_Enc_Diff_table(p_mi_inc => 0
4369 , p_mc_inc => 0
4370 , p_ma_inc => 0
4371 , p_mw_inc => 0
4372 , p_fa_inc => 0
4373 , p_fc_inc => 0
4374 , p_fi_inc =>p_income
4375 , p_fw_inc => 0
4376 , p_total_inc => p_income
4377 , p_ma => 0
4378 , p_mc => 0
4379 , p_mi => 0
4380 , p_mw => 0
4381 , p_fa => 0
4382 , p_fc => 0
4383 , p_fi => 1
4384 , p_fw => 0
4385 , p_total => 1
4386 , p_cat_index => l_cat_index
4387 , p_lev_index => l_lev_index
4388 , p_legal_entity_id => p_legal_entity_id
4389 , p_occupational_level => p_occupational_level
4390 , p_occupational_category => p_occupational_category
4391 , p_occupational_level_id => p_occupational_level_id
4392 , p_occupational_category_id => p_occupational_category_id
4393 );
4394 WHEN 'F02' THEN --female African
4395 ins_g_Enc_Diff_table(p_mi_inc => 0
4396 , p_mc_inc => 0
4397 , p_ma_inc => 0
4398 , p_mw_inc => 0
4399 , p_fa_inc => p_income
4400 , p_fc_inc => 0
4401 , p_fi_inc => 0
4402 , p_fw_inc => 0
4403 , p_total_inc => p_income
4404 , p_ma => 0
4405 , p_mc => 0
4406 , p_mi => 0
4407 , p_mw => 0
4408 , p_fa => 1
4409 , p_fc => 0
4410 , p_fi => 0
4411 , p_fw => 0
4412 , p_total => 1
4413 , p_cat_index => l_cat_index
4414 , p_lev_index => l_lev_index
4415 , p_legal_entity_id => p_legal_entity_id
4416 , p_occupational_level => p_occupational_level
4417 , p_occupational_category => p_occupational_category
4418 , p_occupational_level_id => p_occupational_level_id
4419 , p_occupational_category_id => p_occupational_category_id
4420 );
4421 WHEN 'F03' THEN --female Coloured
4422 ins_g_Enc_Diff_table(p_mi_inc => 0
4423 , p_mc_inc => 0
4424 , p_ma_inc => 0
4425 , p_mw_inc => 0
4426 , p_fa_inc => 0
4427 , p_fc_inc => p_income
4428 , p_fi_inc => 0
4429 , p_fw_inc => 0
4430 , p_total_inc => p_income
4431 , p_ma => 0
4432 , p_mc => 0
4433 , p_mi => 0
4434 , p_mw => 0
4435 , p_fa => 0
4436 , p_fc => 1
4437 , p_fi => 0
4438 , p_fw => 0
4439 , p_total => 1
4440 , p_cat_index => l_cat_index
4441 , p_lev_index => l_lev_index
4442 , p_legal_entity_id => p_legal_entity_id
4443 , p_occupational_level => p_occupational_level
4444 , p_occupational_category => p_occupational_category
4445 , p_occupational_level_id => p_occupational_level_id
4446 , p_occupational_category_id => p_occupational_category_id
4447 );
4448 WHEN 'F04' THEN --female White
4449 ins_g_Enc_Diff_table(p_mi_inc => 0
4450 , p_mc_inc => 0
4451 , p_ma_inc => 0
4452 , p_mw_inc => 0
4453 , p_fa_inc => 0
4454 , p_fc_inc => 0
4455 , p_fi_inc => 0
4456 , p_fw_inc => p_income
4457 , p_total_inc => p_income
4458 , p_ma => 0
4459 , p_mc => 0
4460 , p_mi => 0
4461 , p_mw => 0
4462 , p_fa => 0
4463 , p_fc => 0
4464 , p_fi => 0
4465 , p_fw => 1
4466 , p_total => 1
4467 , p_cat_index => l_cat_index
4468 , p_lev_index => l_lev_index
4469 , p_legal_entity_id => p_legal_entity_id
4470 , p_occupational_level => p_occupational_level
4471 , p_occupational_category => p_occupational_category
4472 , p_occupational_level_id => p_occupational_level_id
4473 , p_occupational_category_id => p_occupational_category_id
4474 );
4475 WHEN 'FZA01' THEN --female Chinese (To be reported as African)
4476 ins_g_Enc_Diff_table(p_mi_inc => 0
4477 , p_mc_inc => 0
4478 , p_ma_inc => 0
4479 , p_mw_inc => 0
4480 , p_fa_inc => p_income
4481 , p_fc_inc => 0
4482 , p_fi_inc => 0
4483 , p_fw_inc => 0
4484 , p_total_inc => p_income
4485 , p_ma => 0
4486 , p_mc => 0
4487 , p_mi => 0
4488 , p_mw => 0
4489 , p_fa => 1
4490 , p_fc => 0
4491 , p_fi => 0
4492 , p_fw => 0
4493 , p_total => 1
4494 , p_cat_index => l_cat_index
4495 , p_lev_index => l_lev_index
4496 , p_legal_entity_id => p_legal_entity_id
4497 , p_occupational_level => p_occupational_level
4498 , p_occupational_category => p_occupational_category
4499 , p_occupational_level_id => p_occupational_level_id
4500 , p_occupational_category_id => p_occupational_category_id
4501 );
4502 else
4503 null;
4504 END case;
4505 END cat_lev_data;
4506
4507 -- Procedure to initialise with the employee details
4508 --
4509 procedure init_g_cat_lev_table
4510 (
4511 p_report_date in per_all_assignments_f.effective_end_date%type,
4512 p_business_group_id in per_all_assignments_f.business_group_id%type,
4513 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
4514 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
4515 ) is
4516
4517 cursor c_assignments is
4518 select paaf.assignment_id,
4519 paaf.person_id, -- Bug 4413678
4520 paaf.payroll_id,
4521 paei.aei_information7 ,
4522 hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4523 hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4524 per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4525 per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4526 paaf.pay_basis_id
4527 from per_assignment_extra_info paei,
4528 per_assignment_status_types past,
4529 per_all_assignments_f paaf,
4530 hr_lookups hl_cat,
4531 hr_lookups hl_lev,
4532 hr_lookups hl_fn
4533 where paaf.business_group_id = p_business_group_id
4534 and p_report_date between paaf.effective_start_date and paaf.effective_end_date
4535 and past.assignment_status_type_id = paaf.assignment_status_type_id
4536 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
4537 and paei.assignment_id = paaf.assignment_id
4538 and paei.information_type = 'ZA_SPECIFIC_INFO'
4539 and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
4540 and paei.aei_information7 is not null
4541 AND hl_cat.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
4542 AND hl_lev.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
4543 AND hl_fn.lookup_type = 'ZA_EE_FUNCTION_TYPE' --Added for Bug 7360563
4544 AND hl_cat.lookup_code <> '15'
4545 AND hl_lev.lookup_code <> '15'
4546 AND hl_fn.lookup_code <>'15'
4547 AND hl_cat.application_id = '800'
4548 AND hl_lev.application_id = '800'
4549 AND hl_fn.application_id = '800'
4550 AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4551 AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4552 AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4553 and nvl(paei.aei_information6, 'N') <> 'Y'
4554 order BY paei.aei_information7, paaf.payroll_id;
4555
4556 l_old_payroll_id per_all_assignments_f.payroll_id%type := -9999;
4557 l_rowind pls_integer;
4558 l_active_days number;
4559 l_ee_income number;
4560 l_ee_annual_income number;
4561 l_report_start date;
4562 l_report_end date;
4563 l_report_date date;
4564 l_difference number;
4565 l_period_frequency per_time_period_types.number_per_fiscal_year%type;
4566 l_ee_balance_type_id pay_balance_types.balance_type_id%type;
4567 l_eea_balance_type_id pay_balance_types.balance_type_id%type;
4568 l_input_value_id pay_input_values_f.input_value_id%type;
4569 l_index number;
4570 l_proc constant varchar2(60) := g_package || 'get_avg_5_lowest_salary';
4571 l_race per_all_people_f.per_information4%type; -- Bug 4413678
4572 l_sex per_all_people_f.sex%type;
4573 --Changes for Bug 7237663
4574 l_er_income number;
4575 l_er_annual_income number;
4576 l_er_balance_type_id pay_balance_types.balance_type_id%type;
4577 l_era_balance_type_id pay_balance_types.balance_type_id%type;
4578
4579
4580 begin
4581 --hr_utility.trace_on(null,'ZAEID');
4582 reset_tables;
4583 hr_utility.set_location('Entering ' || l_proc, 10);
4584
4585 -- Determine whether we need to populate the cache tables
4586 -- Note: No check is made for the validity of the table data, since it is assumed that the
4587 -- reset_tables procedure was called before this procedure.
4588 if g_assignments_table.count = 0 then
4589
4590 hr_utility.set_location('Setup assignments cache', 20);
4591 g_assignments_table.delete;
4592
4593 if p_salary_method = 'BAL' then
4594
4595 -- Get the balance type id's for the normal and annual balances
4596 begin
4597
4598 select balance_type_id
4599 into l_ee_balance_type_id
4600 from pay_balance_types
4601 where balance_name = 'Total Employment Equityable Income'
4602 and legislation_code = 'ZA'
4603 and business_group_id is null;
4604
4605 select balance_type_id
4606 into l_eea_balance_type_id
4607 from pay_balance_types
4608 where balance_name = 'Total Employment Equityable Annual Income'
4609 and legislation_code = 'ZA'
4610 and business_group_id is null;
4611
4612 --Changes for Bug 7237663
4613 select balance_type_id
4614 into l_er_balance_type_id
4615 from pay_balance_types
4616 where balance_name = 'Total Employment Equityable ER Normal Contributions'
4617 and legislation_code = 'ZA'
4618 and business_group_id is null;
4619
4620 select balance_type_id
4621 into l_era_balance_type_id
4622 from pay_balance_types
4623 where balance_name = 'Total Employment Equityable ER Annual Contributions'
4624 and legislation_code = 'ZA'
4625 and business_group_id is null;
4626 --End changes for Bug 7237663
4627
4628 exception
4629 when no_data_found then
4630 raise_application_error(-20000, 'The Employment Equitable balances do not exist.');
4631
4632 end;
4633
4634 end if;
4635
4636 if p_salary_method = 'ELE' then
4637
4638 -- Get the ZA Employment Equity Remuneration element details
4639 begin
4640
4641 select pivf.input_value_id
4642 into l_input_value_id
4643 from pay_input_values_f pivf,
4644 pay_element_types_f petf
4645 where petf.element_name = 'ZA Employment Equity Remuneration'
4646 and petf.business_group_id is null
4647 and petf.legislation_code = 'ZA'
4648 and p_report_date between petf.effective_start_date and petf.effective_end_date
4649 and pivf.element_type_id = petf.element_type_id
4650 and pivf.name = 'Remuneration'
4651 and p_report_date between pivf.effective_start_date and pivf.effective_end_date;
4652
4653 exception
4654 when no_data_found then
4655 raise_application_error(-20004, 'The ZA Employment Equity Remuneration element does not exist.');
4656
4657 end;
4658
4659 end if;
4660
4661 -- Loop through the assignments cursor and populate the assignments table
4662 for l_assignment in c_assignments loop
4663
4664 hr_utility.set_location('ASG ' || l_assignment.assignment_id, 21);
4665
4666 -- Bug 4413678: Begin
4667 Select per_information4, papf.sex
4668 into l_race, l_sex
4669 From per_all_people_f papf
4670 Where papf.person_id = l_assignment.person_id
4671 and p_report_date between papf.effective_start_date and papf.effective_end_date;
4672 -- Bug 4413678: End
4673
4674
4675 if l_assignment.payroll_id is not null and l_race <> 'N' then -- Bug 4413678: Added l_race <> 'Not Used'
4676 -- added RPAHUNE
4677 g_assignments_table(l_assignment.assignment_id).payroll_id := l_assignment.payroll_id;
4678 g_assignments_table(l_assignment.assignment_id).legal_entity_id := l_assignment.aei_information7;
4679 g_assignments_table(l_assignment.assignment_id).occupational_level := l_assignment.occupational_level;
4680 g_assignments_table(l_assignment.assignment_id).occupational_category := l_assignment.occupational_category;
4681 g_assignments_table(l_assignment.assignment_id).occupational_category_ID := l_assignment.occupational_category_id;
4682 g_assignments_table(l_assignment.assignment_id).occupational_level_id := l_assignment.occupational_level_id;
4683 g_assignments_table(l_assignment.assignment_id).race := l_race;
4684 g_assignments_table(l_assignment.assignment_id).sex := l_sex;
4685 hr_utility.set_location('LEGENT ' || l_assignment.aei_information7, 22);
4686
4687 -- Check for a new payroll_id and cache the new payroll details in the payrolls table
4688 if l_assignment.payroll_id <> l_old_payroll_id then
4689
4690 -- Get the start date and end date of the report
4691 begin
4692
4693
4694 l_report_date := p_report_date;
4695 l_difference := 0;
4696
4697 while (l_difference < 355 or l_difference > 375) loop
4698
4699 select ptpf.end_date + 1,
4700 ptpl.end_date
4701 into l_report_start,
4702 l_report_end
4703 from per_time_periods ptpf,
4704 per_time_periods ptpl
4705 where ptpl.payroll_id = l_assignment.payroll_id
4706 and l_report_date between ptpl.start_date and ptpl.end_date
4707 and ptpf.payroll_id = l_assignment.payroll_id
4708 and add_months(l_report_date, -12) + 1 between ptpf.start_date and ptpf.end_date;
4709
4710 l_difference := l_report_end - l_report_start + 1;
4711
4712 if (l_difference < 355 or l_difference > 375) then
4713
4714 l_report_date := l_report_date - 1;
4715
4716 end if;
4717
4718 end loop;
4719
4720 exception
4721 when no_data_found then
4722 begin
4723 select ptpl.end_date
4724 into l_report_end
4725 from per_time_periods ptpl
4726 where ptpl.payroll_id = l_assignment.payroll_id
4727 and p_report_date between ptpl.start_date and ptpl.end_date;
4728
4729 exception
4730 when no_data_found then
4731 Null;
4732 end;
4733
4734 l_report_start := add_months(l_report_end, -12) + 1;
4735
4736 end;
4737
4738 -- Get the payroll period frequency
4739 begin
4740
4741 select ptpt.number_per_fiscal_year
4742 into l_period_frequency
4743 from per_time_period_types ptpt,
4744 pay_all_payrolls_f payr
4745 where payr.payroll_id = l_assignment.payroll_id
4746 and p_report_date between payr.effective_start_date and payr.effective_end_date
4747 and ptpt.period_type = payr.period_type;
4748
4749 exception
4750 when no_data_found then
4751 raise_application_error(-20005, 'The Payroll Period Frequency does not exist.');
4752
4753 end;
4754
4755 l_old_payroll_id := l_assignment.payroll_id;
4756
4757 end if;
4758
4759 hr_utility.set_location('REP_START ' || to_char(l_report_start, 'DD\MM\YYYY'), 22);
4760 hr_utility.set_location('REP_END ' || to_char(l_report_end, 'DD\MM\YYYY'), 23);
4761 hr_utility.set_location('FREQ ' || l_period_frequency, 24);
4762
4763 if p_salary_method = 'BAL' then
4764
4765 -- Get the amount of days the assignment status was Active Assignment
4766 l_active_days := get_active_days
4767 (
4768 p_assignment_id => l_assignment.assignment_id,
4769 p_report_start => l_report_start,
4770 p_report_end => l_report_end
4771 );
4772
4773 hr_utility.set_location('ACT_DAYS ' || l_active_days, 25);
4774
4775 -- Get the Employment Equitable Income
4776 begin
4777
4778 select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
4779 into l_ee_income
4780 from pay_balance_feeds_f pbff,
4781 pay_run_result_values prrv,
4782 pay_run_results prr,
4783 pay_payroll_actions ppa,
4784 pay_assignment_actions paa,
4785 per_assignments_f asg --Bug 4872110
4786 -- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
4787 where paa.assignment_id = l_assignment.assignment_id
4788 and ppa.payroll_action_id = paa.payroll_action_id
4789 and ppa.date_earned between l_report_start and l_report_end
4790 and prr.assignment_action_id = paa.assignment_action_id
4791 and prrv.run_result_id = prr.run_result_id
4792 and pbff.balance_type_id = l_ee_balance_type_id
4793 and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
4794 and prrv.input_value_id = pbff.input_value_id
4795 -- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
4796 and paa.assignment_id = asg.assignment_id
4797 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
4798 and asg.payroll_id = ppa.payroll_id;
4799
4800 exception
4801 when no_data_found then
4802 l_ee_income := 0;
4803
4804 end;
4805
4806 -- Get the Employment Equitable Annual Income
4807 begin
4808
4809 select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
4810 into l_ee_annual_income
4811 from pay_balance_feeds_f pbff,
4812 pay_run_result_values prrv,
4813 pay_run_results prr,
4814 pay_payroll_actions ppa,
4815 pay_assignment_actions paa,
4816 per_assignments_f asg --Bug 4872110
4817 -- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
4818 where paa.assignment_id = l_assignment.assignment_id
4819 and ppa.payroll_action_id = paa.payroll_action_id
4820 and ppa.date_earned between l_report_start and l_report_end
4821 and prr.assignment_action_id = paa.assignment_action_id
4822 and prrv.run_result_id = prr.run_result_id
4823 and pbff.balance_type_id = l_eea_balance_type_id
4824 and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
4825 and prrv.input_value_id = pbff.input_value_id
4826 -- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
4827 and paa.assignment_id = asg.assignment_id
4828 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
4829 and asg.payroll_id = ppa.payroll_id;
4830
4831 exception
4832 when no_data_found then
4833 l_ee_annual_income := 0;
4834
4835 end;
4836
4837 --Changes for Bug 7237663
4838 -- Two new balances added for employer contributions
4839 -- Get the Employment Equitable ER Income
4840 begin
4841
4842 select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
4843 into l_er_income
4844 from pay_balance_feeds_f pbff,
4845 pay_run_result_values prrv,
4846 pay_run_results prr,
4847 pay_payroll_actions ppa,
4848 pay_assignment_actions paa,
4849 per_assignments_f asg
4850 where paa.assignment_id = l_assignment.assignment_id
4851 and ppa.payroll_action_id = paa.payroll_action_id
4852 and ppa.date_earned between l_report_start and l_report_end
4853 and prr.assignment_action_id = paa.assignment_action_id
4854 and prrv.run_result_id = prr.run_result_id
4855 and pbff.balance_type_id = l_er_balance_type_id
4856 and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
4857 and prrv.input_value_id = pbff.input_value_id
4858 and paa.assignment_id = asg.assignment_id
4859 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
4860 and asg.payroll_id = ppa.payroll_id;
4861
4862 exception
4863 when no_data_found then
4864 l_er_income := 0;
4865
4866 end;
4867
4868 -- Get the Employment Equitable ER Annual Income
4869 begin
4870
4871 select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
4872 into l_er_annual_income
4873 from pay_balance_feeds_f pbff,
4874 pay_run_result_values prrv,
4875 pay_run_results prr,
4876 pay_payroll_actions ppa,
4877 pay_assignment_actions paa,
4878 per_assignments_f asg --Bug 4872110
4879 where paa.assignment_id = l_assignment.assignment_id
4880 and ppa.payroll_action_id = paa.payroll_action_id
4881 and ppa.date_earned between l_report_start and l_report_end
4882 and prr.assignment_action_id = paa.assignment_action_id
4883 and prrv.run_result_id = prr.run_result_id
4884 and pbff.balance_type_id = l_era_balance_type_id
4885 and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
4886 and prrv.input_value_id = pbff.input_value_id
4887 and paa.assignment_id = asg.assignment_id
4888 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
4889 and asg.payroll_id = ppa.payroll_id;
4890
4891 exception
4892 when no_data_found then
4893 l_er_annual_income := 0;
4894
4895 end;
4896
4897
4898 hr_utility.set_location('EE_INC ' || l_ee_income, 26);
4899 hr_utility.set_location('EE_ANN ' || l_ee_annual_income, 27);
4900 hr_utility.set_location('ER_INC ' || l_er_income, 26);
4901 hr_utility.set_location('ER_ANN ' || l_er_annual_income, 27);
4902
4903 hr_utility.set_location('STminEND ' || (l_report_end - l_report_start + 1), 28);
4904
4905 -- Calculate the annual income = annualize normal income + annual income
4906 g_assignments_table(l_assignment.assignment_id).annual_income :=
4907 (
4908 ((l_report_end - l_report_start + 1) / l_active_days * l_ee_income) +
4909 ((l_report_end - l_report_start + 1) / l_active_days * l_er_income)
4910 ) + l_ee_annual_income + l_er_annual_income;
4911
4912 hr_utility.set_location('ANSWER ' || g_assignments_table(l_assignment.assignment_id).annual_income, 29);
4913
4914 elsif p_salary_method = 'SAL' then
4915
4916 -- Get the annual salary basis for the current period
4917 begin
4918
4919 select ppp.proposed_salary_n * ppb.pay_annualization_factor
4920 into g_assignments_table(l_assignment.assignment_id).annual_income
4921 from per_pay_proposals ppp,
4922 per_pay_bases ppb
4923 where ppb.pay_basis_id = l_assignment.pay_basis_id
4924 and ppp.assignment_id = l_assignment.assignment_id
4925 and ppp.approved = 'Y'
4926 and ppp.change_date =
4927 (
4928 select max(ppp2.change_date)
4929 from per_pay_proposals ppp2
4930 where ppp2.assignment_id = l_assignment.assignment_id
4931 and ppp2.change_date <= p_report_date
4932 and ppp2.approved = 'Y'
4933 );
4934
4935 exception
4936 when no_data_found then
4937 g_assignments_table(l_assignment.assignment_id).annual_income := 0;
4938
4939 end;
4940
4941 elsif p_salary_method = 'ELE' then
4942
4943 begin
4944
4945 select peevf.screen_entry_value * l_period_frequency
4946 into g_assignments_table(l_assignment.assignment_id).annual_income
4947 from pay_element_entry_values_f peevf,
4948 pay_element_entries_f peef
4949 where peef.assignment_id = l_assignment.assignment_id
4950 and p_report_date between peef.effective_start_date and peef.effective_end_date
4951 and peevf.element_entry_id = peef.element_entry_id
4952 and peevf.input_value_id = l_input_value_id
4953 and p_report_date between peevf.effective_start_date and peevf.effective_end_date;
4954
4955 exception
4956 when no_data_found then
4957 g_assignments_table(l_assignment.assignment_id).annual_income := 0;
4958
4959 end;
4960
4961 end if; -- p_salary_method
4962
4963 end if; -- (l_assignment.payroll_id is not null)
4964
4965 end loop; -- c_assignments
4966
4967 end if; -- g_assignments_table.count = 0
4968
4969 -- The index is calculted by multiplying the legal entity id by 100 and then adding the lookup code
4970 -- This should always give a unique value, since the lookup code is less than 100
4971 -- Start of adding for Employment Equity Report Enhancement Inserting values in table.
4972 l_rowind := g_assignments_table.first;
4973 hr_utility.set_location ('l_rowind :=' || l_rowind, 20);
4974 loop
4975 exit when l_rowind is null;
4976
4977 hr_utility.set_location ('g_assignments_table(l_rowind).legal_entity_id' ||g_assignments_table(l_rowind).legal_entity_id, 20);
4978 hr_utility.set_location ('l_rowind :=' || g_assignments_table(l_rowind).occupational_level, 20);
4979 hr_utility.set_location ('l_rowind :=' || g_assignments_table(l_rowind).occupational_category, 20);
4980 hr_utility.set_location ('l_rowind :=' || g_assignments_table(l_rowind).race, 20);
4981 hr_utility.set_location ('l_rowind :=' || g_assignments_table(l_rowind).sex, 20);
4982 hr_utility.set_location ('l_rowind :=' || g_assignments_table(l_rowind).annual_income, 20);
4983
4984 cat_lev_data( g_assignments_table(l_rowind).legal_entity_id
4985 , g_assignments_table(l_rowind).occupational_level
4986 , g_assignments_table(l_rowind).occupational_category
4987 , g_assignments_table(l_rowind).race
4988 , g_assignments_table(l_rowind).sex
4989 , nvl(g_assignments_table(l_rowind).annual_income,0)
4990 , g_assignments_table(l_rowind).occupational_level_id
4991 , g_assignments_table(l_rowind).occupational_category_ID
4992 );
4993
4994 l_rowind := g_assignments_table.next(l_rowind);
4995 END loop;
4996
4997 l_rowind := g_cat_Enc_Diff_table.first;
4998 loop
4999 exit when l_rowind is null;
5000 INSERT INTO per_za_employment_equity
5001 (
5002 report_id,
5003 reporting_date,
5004 business_group_id,
5005 legal_entity_id,
5006 legal_entity,
5007 disability,
5008 employment_type,
5009 level_cat_code,
5010 level_cat,
5011 ma,
5012 mc,
5013 mi,
5014 mw,
5015 fa,
5016 fc,
5017 fi,
5018 fw,
5019 total
5020 )
5021 Select 'ED1' -- no of employees in each categories
5022 , p_report_date
5023 , p_business_group_id
5024 , g_cat_Enc_Diff_table(l_rowind).legal_entity_id
5025 , haou.name
5026 , null
5027 , null
5028 , g_cat_Enc_Diff_table(l_rowind).occupational_code_id
5029 , g_cat_Enc_Diff_table(l_rowind).occupational_code
5030 , g_cat_Enc_Diff_table(l_rowind).ma
5031 , g_cat_Enc_Diff_table(l_rowind).mc
5032 , g_cat_Enc_Diff_table(l_rowind).mi
5033 , g_cat_Enc_Diff_table(l_rowind).mw
5034 , g_cat_Enc_Diff_table(l_rowind).fa
5035 , g_cat_Enc_Diff_table(l_rowind).fc
5036 , g_cat_Enc_Diff_table(l_rowind).fi
5037 , g_cat_Enc_Diff_table(l_rowind).fw
5038 , g_cat_Enc_Diff_table(l_rowind).total
5039 FROM hr_all_organization_units haou
5040 Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
5041
5042
5043 INSERT INTO per_za_employment_equity
5044 (
5045 report_id,
5046 reporting_date,
5047 business_group_id,
5048 legal_entity_id,
5049 legal_entity,
5050 disability,
5051 employment_type,
5052 level_cat_code,
5053 level_cat,
5054 ma,
5055 mc,
5056 mi,
5057 mw,
5058 fa,
5059 fc,
5060 fi,
5061 fw,
5062 total
5063 )
5064 Select 'ED1I' -- income
5065 , p_report_date
5066 , p_business_group_id
5067 , g_cat_Enc_Diff_table(l_rowind).legal_entity_id
5068 , haou.name
5069 , null
5070 , null
5071 , g_cat_Enc_Diff_table(l_rowind).occupational_code_id
5072 , g_cat_Enc_Diff_table(l_rowind).occupational_code
5073 , g_cat_Enc_Diff_table(l_rowind).ma_inc
5074 , g_cat_Enc_Diff_table(l_rowind).mc_inc
5075 , g_cat_Enc_Diff_table(l_rowind).mi_inc
5076 , g_cat_Enc_Diff_table(l_rowind).mw_inc
5077 , g_cat_Enc_Diff_table(l_rowind).fa_inc
5078 , g_cat_Enc_Diff_table(l_rowind).fc_inc
5079 , g_cat_Enc_Diff_table(l_rowind).fi_inc
5080 , g_cat_Enc_Diff_table(l_rowind).fw_inc
5081 , g_cat_Enc_Diff_table(l_rowind).total_inc
5082 FROM hr_all_organization_units haou
5083 Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
5084
5085 l_rowind := g_cat_Enc_Diff_table.next(l_rowind);
5086 END loop;
5087
5088 l_rowind := g_lev_Enc_Diff_table.first;
5089 loop
5090 exit when l_rowind is null;
5091 INSERT INTO per_za_employment_equity
5092 (
5093 report_id,
5094 reporting_date,
5095 business_group_id,
5096 legal_entity_id,
5097 legal_entity,
5098 disability,
5099 employment_type,
5100 level_cat_code,
5101 level_cat,
5102 ma,
5103 mc,
5104 mi,
5105 mw,
5106 fa,
5107 fc,
5108 fi,
5109 fw,
5110 total
5111 )
5112 Select 'ED2' -- no of employees in each categories
5113 , p_report_date
5114 , p_business_group_id
5115 , g_lev_Enc_Diff_table(l_rowind).legal_entity_id
5116 , haou.name legal_entity
5117 , null
5118 , null
5119 , g_lev_Enc_Diff_table(l_rowind).occupational_code_id
5120 , g_lev_Enc_Diff_table(l_rowind).occupational_code
5121 , g_lev_Enc_Diff_table(l_rowind).ma
5122 , g_lev_Enc_Diff_table(l_rowind).mc
5123 , g_lev_Enc_Diff_table(l_rowind).mi
5124 , g_lev_Enc_Diff_table(l_rowind).mw
5125 , g_lev_Enc_Diff_table(l_rowind).fa
5126 , g_lev_Enc_Diff_table(l_rowind).fc
5127 , g_lev_Enc_Diff_table(l_rowind).fi
5128 , g_lev_Enc_Diff_table(l_rowind).fw
5129 , g_lev_Enc_Diff_table(l_rowind).total
5130 FROM hr_all_organization_units haou
5131 Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
5132
5133 INSERT INTO per_za_employment_equity
5134 (
5135 report_id,
5136 reporting_date,
5137 business_group_id,
5138 legal_entity_id,
5139 legal_entity,
5140 disability,
5141 employment_type,
5142 level_cat_code,
5143 level_cat,
5144 ma,
5145 mc,
5146 mi,
5147 mw,
5148 fa,
5149 fc,
5150 fi,
5151 fw,
5152 total
5153 )
5154 Select 'ED2I' -- income
5155 , p_report_date
5156 , p_business_group_id
5157 , g_lev_Enc_Diff_table(l_rowind).legal_entity_id
5158 , haou.name legal_entity
5159 , null
5160 , null
5161 , g_lev_Enc_Diff_table(l_rowind).occupational_code_id
5162 , g_lev_Enc_Diff_table(l_rowind).occupational_code
5163 , g_lev_Enc_Diff_table(l_rowind).ma_inc
5164 , g_lev_Enc_Diff_table(l_rowind).mc_inc
5165 , g_lev_Enc_Diff_table(l_rowind).mi_inc
5166 , g_lev_Enc_Diff_table(l_rowind).mw_inc
5167 , g_lev_Enc_Diff_table(l_rowind).fa_inc
5168 , g_lev_Enc_Diff_table(l_rowind).fc_inc
5169 , g_lev_Enc_Diff_table(l_rowind).fi_inc
5170 , g_lev_Enc_Diff_table(l_rowind).fw_inc
5171 , g_lev_Enc_Diff_table(l_rowind).total_inc
5172 FROM hr_all_organization_units haou
5173 Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
5174
5175 l_rowind := g_lev_Enc_Diff_table.next(l_rowind);
5176 END loop;
5177 --hr_utility.trace_off;
5178
5179 -- Inserts non-associated occupational categories with zero values for no of employees
5180 insert into per_za_employment_equity
5181 (
5182 report_id,
5183 reporting_date,
5184 business_group_id,
5185 legal_entity_id,
5186 legal_entity,
5187 disability,
5188 employment_type,
5189 level_cat_code,
5190 level_cat,
5191 MA,
5192 MC,
5193 MI,
5194 MW,
5195 FA,
5196 FC,
5197 FI,
5198 FW,
5199 total
5200 )
5201 select 'ED1' report_id,
5202 p_report_date reporting_date,
5203 p_business_group_id business_group_id,
5204 haou.organization_id legal_entity_id,
5205 haou.name legal_entity,
5206 Null disability,
5207 Null employment_type,
5208 hl.lookup_code level_cat_code,
5209 hl.meaning ,
5210 0 MA,
5211 0 MC,
5212 0 MI,
5213 0 MW,
5214 0 FA,
5215 0 FC,
5216 0 FI,
5217 0 FW,
5218 0 total
5219 from hr_lookups hl
5220 , hr_all_organization_units haou
5221 where not exists
5222 (
5223 select 'X'
5224 from per_za_employment_equity pzee
5225 where pzee.level_cat_code = hl.lookup_code
5226 and pzee.report_id = 'ED1'
5227 and pzee.business_group_id = p_business_group_id --Bug 4872110
5228 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
5229 )
5230 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
5231 and haou.business_group_id = p_business_group_id --Bug 4872110
5232 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5233
5234 -- Inserts non-associated occupational categories with zero values for no of employees
5235 insert into per_za_employment_equity
5236 (
5237 report_id,
5238 reporting_date,
5239 business_group_id,
5240 legal_entity_id,
5241 legal_entity,
5242 disability,
5243 employment_type,
5244 level_cat_code,
5245 level_cat,
5246 MA,
5247 MC,
5248 MI,
5249 MW,
5250 FA,
5251 FC,
5252 FI,
5253 FW,
5254 total
5255 )
5256 select 'ED1I' report_id,
5257 p_report_date reporting_date,
5258 p_business_group_id business_group_id,
5259 haou.organization_id legal_entity_id,
5260 haou.name legal_entity,
5261 Null disability,
5262 Null employment_type,
5263 hl.lookup_code level_cat_code,
5264 hl.meaning,
5265 0 MA,
5266 0 MC,
5267 0 MI,
5268 0 MW,
5269 0 FA,
5270 0 FC,
5271 0 FI,
5272 0 FW,
5273 0 total
5274 from hr_lookups hl
5275 , hr_all_organization_units haou
5276 where not exists
5277 (
5278 select 'X'
5279 from per_za_employment_equity pzee
5280 where pzee.level_cat_code = hl.lookup_code
5281 and pzee.report_id = 'ED1I'
5282 and pzee.business_group_id = p_business_group_id --Bug 4872110
5283 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
5284 )
5285 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
5286 and haou.business_group_id = p_business_group_id --Bug 4872110
5287 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5288
5289 -- inserting 0 values for the no of employees
5290 insert into per_za_employment_equity
5291 (
5292 report_id,
5293 reporting_date,
5294 business_group_id,
5295 legal_entity_id,
5296 legal_entity,
5297 disability,
5298 employment_type,
5299 level_cat_code,
5300 level_cat,
5301 MA,
5302 MC,
5303 MI,
5304 MW,
5305 FA,
5306 FC,
5307 FI,
5308 FW,
5309 total
5310 )
5311 select 'ED2' report_id,
5312 p_report_date reporting_date,
5313 p_business_group_id business_group_id,
5314 haou.organization_id legal_entity_id,
5315 haou.name legal_entity,
5316 null disability,
5317 null employment_type,
5318 hl.lookup_code level_cat_code,
5319 hl.meaning,
5320 0 MA,
5321 0 MC,
5322 0 MI,
5323 0 MW,
5324 0 FA,
5325 0 FC,
5326 0 FI,
5327 0 FW,
5328 0 total
5329 from hr_lookups hl
5330 , hr_all_organization_units haou
5331 where not exists
5332 (
5333 select 'X'
5334 from per_za_employment_equity pzee
5335 where pzee.level_cat_code = hl.lookup_code
5336 and pzee.report_id = 'ED2'
5337 and pzee.business_group_id = p_business_group_id --Bug 4872110
5338 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
5339 )
5340 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
5341 and haou.business_group_id = p_business_group_id --Bug 4872110
5342 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5343
5344 -- inserting 0 values for the Income
5345 insert into per_za_employment_equity
5346 (
5347 report_id,
5348 reporting_date,
5349 business_group_id,
5350 legal_entity_id,
5351 legal_entity,
5352 disability,
5353 employment_type,
5354 level_cat_code,
5355 level_cat,
5356 MA,
5357 MC,
5358 MI,
5359 MW,
5360 FA,
5361 FC,
5362 FI,
5363 FW,
5364 total
5365 )
5366 select 'ED2I' report_id,
5367 p_report_date reporting_date,
5368 p_business_group_id business_group_id,
5369 haou.organization_id legal_entity_id,
5370 haou.name legal_entity,
5371 null disability,
5372 null employment_type,
5373 hl.lookup_code level_cat_code,
5374 hl.meaning ,
5375 0 MA,
5376 0 MC,
5377 0 MI,
5378 0 MW,
5379 0 FA,
5380 0 FC,
5381 0 FI,
5382 0 FW,
5383 0 total
5384 from hr_lookups hl
5385 , hr_all_organization_units haou
5386 where not exists
5387 (
5388 select 'X'
5389 from per_za_employment_equity pzee
5390 where pzee.level_cat_code = hl.lookup_code
5391 and pzee.report_id = 'ED2I'
5392 and pzee.business_group_id = p_business_group_id --Bug 4872110
5393 and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
5394 )
5395 and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
5396 and haou.business_group_id = p_business_group_id --Bug 4872110
5397 and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5398
5399 commit;
5400
5401 end init_g_cat_lev_table;
5402
5403 end per_za_employment_equity_pkg; -- package body