[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert procedures.
F.D. Loubser 11 Sep 2001 115.4 Almost complete rewrite for 11i.
F.D. Loubser 10 Dec 2001 115.7 Business_group_id on user table
F.D. Loubser 1 Feb 2002 115.8 QA fixes
F.D. Loubser 1 Feb 2002 115.9 Added checkfile
F.D. Loubser 7 Feb 2002 115.10 Removed low list skip optimization
F.D. Loubser 14 Feb 2002 115.11 Added multiple legal entity
J.N. Louw 5 Apr 2002 115.12 Bug 2306877
Fixed default select statements
each section EQ1 to 6
F.D. Loubser 9 May 2002 115.13 g_cat_flex variable too small
Nirupa S 05 Dec 2002 115.14 2686695 In accordance with the APPS-wide
performance changes for 11.5.9,
NOCOPY has been added to all
OUT and IN OUT parameters.
Nirupa S 10 Dec 2002 115.14 2686695 Added exception handling for
NOCOPY in calc_highest_and_lowest_avg
A.Sengar 11 Dec 2002 115.16 2665394 Modified the query to improve
performance.
Nageswara 17 Nov 2004 115.17 3962073 modified query to fetch Employee Type
when user has entered value.
Supressed GSCC warnings
Nageswara 24 Nov 2004 115.18 4027769 modified select query to all disabilities
'F','P' as 'Y' (Disabled)
Kaladhaur P 28-Jun-2005 115.20 4445926 Fix GSCC Error: File.Sql.8 and File.Sql.18
Kaladhaur P 28-Jun-2005 115.21 4413678 Function get_avg_5_lowest_salary has been
modified to process employees with race
not equal to 'Not Used'.
A. Mahanty 19-Dec-2005 115.24 4872110 R12 Performance Bug fix. Modified the query in
function get_avg_5_lowest_salary
A. Mahanty 21-Dec-2005 115.25 4872110 R12 Performance Bug fix. Modified the queries in
function populate_ee_table
Kaladhaur P 22-May-2006 115.26 4413678 Function get_avg_5_lowest_salary has been
modified to process employees with race
not equal to 'Not Used'.
Kaladhaur P 20-Jun-2006 115.27 5228238 Query in function get_lookup_code has been
modified to fetch one row for lookup meaning
'Not Applicable'.
Kaladhaur P 04-Jul-2006 115.28 4413678 Modified the comments.
R Pahune 24-Jul-2006 115.30 5406242 Employment equity enhancment.
R Pahune 08-jan-2008 115.43 6773326 To get correct Employment Category due to
changes for WSP.
R Babla 18-Feb-2008 115.49 6817148 Changes in populate_ee_table to consider the
new segment added for foreign national
R Babla 23-Jul-2008 115.50 7277745/Changes done to consider chinese as Africans
7237663 and to cater for employer contribution
R Babla 30-Jul-2008 115.51 7277745/Changes done to add 'Normal' to the balance name
7237663 for Normal ER contribution
P Arusia 25-Aug-2008 115.52 7277745 Corrected the procedure
init_g_cat_lev_table to add l_er_annual_income
to net annual income
R Babla 30-Jul-2008 115.54 7360563 Changes done in cursor c_assignments of
proc init_g_cat_lev_table so as not to
include employee in differential report
which has any of occupational cat/ level
or function type as 'Not Applicable'
R Babla 16-Sep-2009 115.56 8911880 Changes done in procedure init_g_cat_lev_table
to ensure the income differential report doesnt annualize
the income for employees who did not work for completing
reporting year + show actual remuneration for fluctuating
income.
NCHINNAM 17-Nov-2009 115.57 8486088 1. Commented 'EQ1' - Occupational Categories
2. Changed Termination Categories
3. Changed foreign national logic
NCHINNAM 17-Nov-2009 115.58 Fixed GSCC Errors
R Babla 24-Nov-2009 115.59 9112237 Added init_g_cat_lev_new_table and supporting procedures from
reporting year 2009
R Babla 24-Nov-2009 115.60 9112237 Fixed GSCC Warnings
NCHINNAM 01-Dec-2009 115.61 9112237 Added new procedures from
reporting year 2009
NCHINNAM 03-Dec-2009 115.62 9112237 renaming the reason is moved to dt.
R Babla 18-Dec-2009 115.63 9112237 Modified query for inserting rows if row present for foreigner
non permanent, but not present for non foreigner non permanent
in procedure init_g_cat_lev_new_table
R Babla 01-Apr-2010 115.64 9462039 Modified procedures to remove the dependency on Occupation Category
from reporting year 2009
ABDASH 15-Oct-2012 115.65 14690729 Modified the query for 'EQ8' in procedure "populate_ee_table_new"
to avoid duplication of employees when multiple leaving_reasons
are mapped to a single termination category.
==============================================================================
*/
-- Global types
type r_assignments is record
(
payroll_id per_all_assignments_f.payroll_id%type,
legal_entity_id hr_all_organization_units.organization_id%type,
legal_entity hr_all_organization_units.name%type,
occupational_level_id hr_lookups.lookup_code%type,
occupational_category_id hr_lookups.lookup_code%type,
occupational_level hr_lookups.meaning%type,
occupational_category hr_lookups.meaning%type,
race per_all_people_f.per_information4%type,
sex per_all_people_f.sex%type,
annual_income number
);
select sum
(
decode(sign(p_report_end - paaf.effective_end_date), 1, paaf.effective_end_date, p_report_end)
-
decode(sign(p_report_start - paaf.effective_start_date), 1, p_report_start, paaf.effective_start_date)
+ 1
)
into l_count
from per_assignment_status_types past,
per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and past.assignment_status_type_id = paaf.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and paaf.effective_start_date <= p_report_end
and paaf.effective_end_date >= p_report_start;
select pucifcat.value
into l_termination_reason
from pay_user_column_instances_f pucifcat,
pay_user_rows_f purfcat,
pay_user_columns puccat,
pay_user_rows_f purfqc,
pay_user_column_instances_f pucifqc,
pay_user_columns pucqc,
pay_user_tables put
where put.user_table_name = 'ZA_TERMINATION_CATEGORIES'
and put.business_group_id is null
and put.legislation_code = 'ZA'
and pucqc.user_table_id = put.user_table_id
and pucqc.user_column_name = 'Lookup Code'
and pucifqc.user_column_id = pucqc.user_column_id
and pucifqc.business_group_id = p_business_group_id
and p_report_date between pucifqc.effective_start_date and pucifqc.effective_end_date
and pucifqc.value = p_reason_code
and purfqc.user_table_id = put.user_table_id
and purfqc.user_row_id = pucifqc.user_row_id
and purfqc.business_group_id = pucifqc.business_group_id
and p_report_date between purfqc.effective_start_date and purfqc.effective_end_date
and puccat.user_table_id = put.user_table_id
and puccat.user_column_name = 'Termination Category'
and purfcat.user_table_id = put.user_table_id
and purfcat.business_group_id = pucifqc.business_group_id
and p_report_date between purfcat.effective_start_date and purfcat.effective_end_date
and purfcat.row_low_range_or_name = purfqc.row_low_range_or_name
and pucifcat.user_column_id = puccat.user_column_id
and pucifcat.user_row_id = purfcat.user_row_id
and p_report_date between pucifcat.effective_start_date and pucifcat.effective_end_date
and pucifcat.business_group_id = pucifqc.business_group_id
and pucifcat.value in
(
'Resignation',
'Non-Renewal of Contract',
'Dismissal - Operational Requirements',
'Dismissal - Misconduct',
'Dismissal - Incapacity',
'Other'
);
g_assignments_table.delete;
g_cat_averages_table.delete;
g_lev_averages_table.delete;
g_cat_Enc_Diff_table.delete;
g_lev_Enc_Diff_table.delete;
DELETE FROM per_za_employment_equity
Where report_id IN
( 'ED1', 'ED2', 'ED1I','ED2I');
select paaf.assignment_id,
paaf.person_id, -- Bug 4413678
paaf.payroll_id,
paei.aei_information7,
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,
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,
paaf.pay_basis_id
from per_assignment_extra_info paei,
per_assignment_status_types past,
per_all_assignments_f paaf
where paaf.business_group_id = p_business_group_id
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and past.assignment_status_type_id = paaf.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legent_param, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
order by paaf.payroll_id;
g_assignments_table.delete;
select balance_type_id
into l_ee_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable Income'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_eea_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable Annual Income'
and legislation_code = 'ZA'
and business_group_id is null;
select pivf.input_value_id
into l_input_value_id
from pay_input_values_f pivf,
pay_element_types_f petf
where petf.element_name = 'ZA Employment Equity Remuneration'
and petf.business_group_id is null
and petf.legislation_code = 'ZA'
and p_report_date between petf.effective_start_date and petf.effective_end_date
and pivf.element_type_id = petf.element_type_id
and pivf.name = 'Remuneration'
and p_report_date between pivf.effective_start_date and pivf.effective_end_date;
Select per_information4
into l_race
From per_all_people_f papf
Where papf.person_id = l_assignment.person_id
and p_report_date between papf.effective_start_date and papf.effective_end_date;
select ptpf.end_date + 1,
ptpl.end_date
into l_report_start,
l_report_end
from per_time_periods ptpf,
per_time_periods ptpl
where ptpl.payroll_id = l_assignment.payroll_id
and l_report_date between ptpl.start_date and ptpl.end_date
and ptpf.payroll_id = l_assignment.payroll_id
and add_months(l_report_date, -12) + 1 between ptpf.start_date and ptpf.end_date;
select ptpl.end_date
into l_report_end
from per_time_periods ptpl
where ptpl.payroll_id = l_assignment.payroll_id
and p_report_date between ptpl.start_date and ptpl.end_date;
select ptpt.number_per_fiscal_year
into l_period_frequency
from per_time_period_types ptpt,
pay_all_payrolls_f payr
where payr.payroll_id = l_assignment.payroll_id
and p_report_date between payr.effective_start_date and payr.effective_end_date
and ptpt.period_type = payr.period_type;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_ee_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
-- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between l_report_start and l_report_end
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_ee_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
-- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
and paa.assignment_id = asg.assignment_id
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_ee_annual_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
-- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between l_report_start and l_report_end
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_eea_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
-- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
and paa.assignment_id = asg.assignment_id
and asg.payroll_id = ppa.payroll_id;
select ppp.proposed_salary_n * ppb.pay_annualization_factor
into g_assignments_table(l_assignment.assignment_id).annual_income
from per_pay_proposals ppp,
per_pay_bases ppb
where ppb.pay_basis_id = l_assignment.pay_basis_id
and ppp.assignment_id = l_assignment.assignment_id
and ppp.approved = 'Y'
and ppp.change_date =
(
select max(ppp2.change_date)
from per_pay_proposals ppp2
where ppp2.assignment_id = l_assignment.assignment_id
and ppp2.change_date <= p_report_date
and ppp2.approved = 'Y'
);
select peevf.screen_entry_value * l_period_frequency
into g_assignments_table(l_assignment.assignment_id).annual_income
from pay_element_entry_values_f peevf,
pay_element_entries_f peef
where peef.assignment_id = l_assignment.assignment_id
and p_report_date between peef.effective_start_date and peef.effective_end_date
and peevf.element_entry_id = peef.element_entry_id
and peevf.input_value_id = l_input_value_id
and p_report_date between peevf.effective_start_date and peevf.effective_end_date;
select date_start,
actual_termination_date,
projected_termination_date
into l_date_start,
l_actual_termination_date,
l_projected_termination_date
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
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 = '
|| to_char(p_job_id)
|| ' and pjd.job_definition_id = pj.job_definition_id '
|| ' and flv1.lookup_type = '||'''ZA_WSP_OCCUPATIONAL_CATEGORIES'''
|| ' and flv1.lookup_code = pjd.' || p_segment
|| ' and flv2.lookup_type(+) = '||'''ZA_EMP_EQ_OCCUPATIONAL_CAT'''
|| ' and flv2.lookup_code(+) = flv1.attribute1'
|| ' and flv1.security_group_id = fnd_global.lookup_security_group(flv1.lookup_type,3)'
|| ' and flv1.language = userenv('||'''LANG'''||')'
|| ' and flv2.language(+) = userenv('||'''LANG'''||')';
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 = '
|| to_char(p_grade_id)
|| ' and pgd.grade_definition_id = pg.grade_definition_id '
|| ' and flv1.lookup_type = '||'''ZA_WSP_OCCUPATIONAL_CATEGORIES'''
|| ' and flv1.lookup_code = pgd.' || p_segment
|| ' and flv2.lookup_type(+) = '||'''ZA_EMP_EQ_OCCUPATIONAL_CAT'''
|| ' and flv2.lookup_code(+) = flv1.attribute1'
|| ' and flv1.security_group_id = fnd_global.lookup_security_group(flv1.lookup_type,3)'
|| ' and flv1.language = userenv('||'''LANG'''||')'
|| ' and flv2.language(+) = userenv('||'''LANG'''||')';
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 = '
|| to_char(p_position_id)
|| ' and ppd.position_definition_id = pap.position_definition_id '
|| ' and flv1.lookup_type = '||'''ZA_WSP_OCCUPATIONAL_CATEGORIES'''
|| ' and flv1.lookup_code = ppd.' || p_segment
|| ' and flv2.lookup_type(+) = '||'''ZA_EMP_EQ_OCCUPATIONAL_CAT'''
|| ' and flv2.lookup_code(+) = flv1.attribute1'
|| ' and flv1.security_group_id = fnd_global.lookup_security_group(flv1.lookup_type,3)'
|| ' and flv1.language = userenv('||'''LANG'''||')'
|| ' and flv2.language(+) = userenv('||'''LANG'''||')';
Select COUNT(*)
INTO l_indicator
FROM hr_lookups
WHERE lookup_type = 'ZA_WSP_OCCUPATIONAL_CATEGORIES';
l_sql := 'select hl.meaning from hr_lookups hl, per_job_definitions pjd, per_jobs pj where pj.job_id = '
|| to_char(p_job_id)
|| ' and pjd.job_definition_id = pj.job_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EMP_EQ_OCCUPATIONAL_'
|| p_type || ''' and hl.lookup_code = pjd.' || p_segment;
l_sql := 'select hl.meaning from hr_lookups hl, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
|| to_char(p_grade_id)
|| ' and pgd.grade_definition_id = pg.grade_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EMP_EQ_OCCUPATIONAL_'
|| p_type || ''' and hl.lookup_code = pgd.' || p_segment;
l_sql := 'select hl.meaning from hr_lookups hl, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
|| to_char(p_position_id)
|| ' and ppd.position_definition_id = pap.position_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EMP_EQ_OCCUPATIONAL_'
|| p_type || ''' and hl.lookup_code = ppd.' || p_segment;
select user_table_id
into l_user_table_id
from pay_user_tables
where user_table_name = 'ZA_OCCUPATIONAL_TYPES'
and business_group_id is null
and legislation_code = 'ZA';
select user_column_id
into l_user_column_id_flex
from pay_user_columns
where user_table_id = l_user_table_id
and business_group_id is null
and legislation_code = 'ZA'
and user_column_name = 'Flexfield';
select user_column_id
into l_user_column_id_seg
from pay_user_columns
where user_table_id = l_user_table_id
and business_group_id is null
and legislation_code = 'ZA'
and user_column_name = 'Segment';
select user_row_id
into l_user_row_id_cat
from pay_user_rows_f
where user_table_id = l_user_table_id
and row_low_range_or_name = 'Occupational Categories'
and p_report_date between effective_start_date and effective_end_date;
select value
into g_cat_flex
from pay_user_column_instances_f
where user_row_id = l_user_row_id_cat
and user_column_id = l_user_column_id_flex
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select value
into g_cat_segment
from pay_user_column_instances_f
where user_row_id = l_user_row_id_cat
and user_column_id = l_user_column_id_seg
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select user_row_id
into l_user_row_id_lev
from pay_user_rows_f
where user_table_id = l_user_table_id
and row_low_range_or_name = 'Occupational Levels'
and p_report_date between effective_start_date and effective_end_date;
select user_row_id
into l_user_row_id_func
from pay_user_rows_f
where user_table_id = l_user_table_id
and row_low_range_or_name = 'Function Type'
and p_report_date between effective_start_date and effective_end_date;
select value
into g_lev_flex
from pay_user_column_instances_f
where user_row_id = l_user_row_id_lev
and user_column_id = l_user_column_id_flex
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select value
into g_Func_flex
from pay_user_column_instances_f
where user_row_id = l_user_row_id_func
and user_column_id = l_user_column_id_flex
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select value
into g_lev_segment
from pay_user_column_instances_f
where user_row_id = l_user_row_id_lev
and user_column_id = l_user_column_id_seg
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select value
into g_Func_segment
from pay_user_column_instances_f
where user_row_id = l_user_row_id_func
and user_column_id = l_user_column_id_seg
and business_group_id = p_business_group_id
and p_report_date between effective_start_date and effective_end_date;
select distinct hl.lookup_code
into l_lookup_code
from hr_lookups hl
where hl.lookup_type in ('ZA_EMP_EQ_OCCUPATIONAL_CAT', 'ZA_EMP_EQ_OCCUPATIONAL_LEV')
and hl.meaning = p_meaning;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and p_report_date between papf.effective_start_date and papf.effective_end_date
and papf.current_employee_flag = 'Y'
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code <> '15' -- Not Applicable.
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
AND hl2.lookup_code <> '15' -- Not Applicable.
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)
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ1' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ1'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ1F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ1F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and p_report_date between papf.effective_start_date and papf.effective_end_date
and papf.current_employee_flag = 'Y'
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id,paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code <> '15' -- Operation / core function
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F')));
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ2' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ2'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ2F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ2F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and p_report_date between papf.effective_start_date and papf.effective_end_date
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code = '1' -- Operation / core function
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F')))
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ3' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ3'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ3F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ3F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and p_report_date between papf.effective_start_date and papf.effective_end_date
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code = '2' -- Support function
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F')))
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ4' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ4'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ4F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ4F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
and ppos.date_start between add_months(p_report_date, -12) + 1 and p_report_date
and papf.effective_start_date = ppos.date_start
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_start_date = ppos.date_start
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable.
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F')))
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ5' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ5'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ5F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ5F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
- to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
hl.lookup_code lookup_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) > add_months(p_report_date, -12) + 1
and ppos.date_start < p_report_date
and papf.effective_start_date = ppos.date_start
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_start_date between ppos.date_start and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paaf.effective_start_date > add_months(p_report_date, -12) + 1
and paaf.effective_start_date <= p_report_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable.
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl2.lookup_code <> '15' -- Not Applicable.
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') <
any
(
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
from per_all_assignments_f paaf1
where paaf1.person_id = papf.person_id
and paaf1.primary_flag = 'Y'
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
and paaf1.effective_end_date + 1 = paaf.effective_start_date
)
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))) ;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ6' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ6'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ6F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ6F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
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
and papf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
)
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ7' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ7'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ7F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ7F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
decode
(
tpa.termination_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
'Other', 6,
null
) meaning_code,
decode
(
tpa.termination_reason,
'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
tpa.termination_reason
),
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
ppos.leaving_reason meaning_code,
nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
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
and papf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
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'
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'
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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)
and hl.lookup_code <> '15' -- Not Applicable
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
and hl2.lookup_code <> '15' -- Not Applicable
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
ppos.leaving_reason,
nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION3,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
-to_char(p_report_date,'YYYYMMDD'))
,-1,null,'F'))
)
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.termination_reason;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ8' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
decode
(
l_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
'Other', 6,
null
) level_cat_code,
decode
(
l_reason,
'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
l_reason
) level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat = l_reason
and pzee.report_id = 'EQ8'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ8F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
decode
(
l_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
'Other', 6,
null
) level_cat_code,
decode
(
l_reason,
'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
l_reason
) level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat = l_reason
and pzee.report_id = 'EQ8F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
l_sql := 'select hl.meaning from hr_lookups hl, per_job_definitions pjd, per_jobs pj where pj.job_id = '
|| to_char(p_job_id)
|| ' and pjd.job_definition_id = pj.job_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EE_FUNCTION_TYPE'
|| ''' and hl.lookup_code = pjd.' || p_segment;
l_sql := 'select hl.meaning from hr_lookups hl, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
|| to_char(p_grade_id)
|| ' and pgd.grade_definition_id = pg.grade_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EE_FUNCTION_TYPE'
|| ''' and hl.lookup_code = pgd.' || p_segment;
l_sql := 'select hl.meaning from hr_lookups hl, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
|| to_char(p_position_id)
|| ' and ppd.position_definition_id = pap.position_definition_id and hl.application_id = 800 and hl.lookup_type = ''ZA_EE_FUNCTION_TYPE'
|| ''' and hl.lookup_code = ppd.' || p_segment;
DELETE FROM per_za_employment_equity
Where REPORT_ID IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8',
'EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F'
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select substr(report_id,1,3),
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
0,
0,
0,
0,
0,
0,
0,
0,
0
from per_za_employment_equity pzee1
Where pzee1.business_group_id = p_business_group_id
AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
AND not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
AND pzee.legal_entity_id = pzee1.legal_entity_id
AND pzee.report_id ||'F' = pzee1.report_id
AND pzee1.level_cat_code = pzee.level_cat_code
AND pzee1.level_cat = pzee.level_cat
and nvl(pzee.disability,'X') = nvl(pzee1.disability,'X')
and pzee.employment_type = pzee1.employment_type
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select report_id||'F' report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
0,
0,
0,
0,
0,
0,
0,
0,
0
from per_za_employment_equity pzee1
Where pzee1.business_group_id = p_business_group_id
AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
AND not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
AND pzee.legal_entity_id = pzee1.legal_entity_id
AND pzee1.report_id ||'F' = pzee.report_id
AND pzee1.level_cat_code = pzee.level_cat_code
AND pzee1.level_cat = pzee.level_cat
and nvl(pzee.disability,'X') = nvl(pzee1.disability,'X')
and pzee.employment_type = pzee1.employment_type
);
select paaf.assignment_id,
paaf.person_id, -- Bug 4413678
paaf.payroll_id,
paei.aei_information7 ,
hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
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,
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,
paaf.pay_basis_id
from per_assignment_extra_info paei,
per_assignment_status_types past,
per_all_assignments_f paaf,
hr_lookups hl_cat,
hr_lookups hl_lev,
hr_lookups hl_fn
where paaf.business_group_id = p_business_group_id
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and past.assignment_status_type_id = paaf.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
AND hl_cat.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
AND hl_lev.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
AND hl_fn.lookup_type = 'ZA_EE_FUNCTION_TYPE' --Added for Bug 7360563
AND hl_cat.lookup_code <> '15'
AND hl_lev.lookup_code <> '15'
AND hl_fn.lookup_code <>'15'
AND hl_cat.application_id = '800'
AND hl_lev.application_id = '800'
AND hl_fn.application_id = '800'
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)
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)
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)
and nvl(paei.aei_information6, 'N') <> 'Y'
order BY paei.aei_information7, paaf.payroll_id;
select paaf.assignment_id,
paaf.person_id, -- Bug 4413678
paaf.payroll_id,
paei.aei_information7 ,
hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
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,
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,
paaf.pay_basis_id
from per_assignment_extra_info paei,
per_all_assignments_f paaf,
hr_lookups hl_cat,
hr_lookups hl_lev,
hr_lookups hl_fn
where paaf.business_group_id = p_business_group_id
and (add_months(p_report_date,-12)+1 <=paaf.effective_end_date and p_report_date >=paaf.effective_start_date)
and paaf.effective_end_date = ( select max(paaf1.effective_end_date)
from per_all_assignments_f paaf1,
per_assignment_status_types past
where paaf1.assignment_id = paaf.assignment_id
and paaf1.effective_start_date <= p_report_date
and past.assignment_status_type_id = paaf1.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
)
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
AND hl_cat.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
AND hl_lev.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
AND hl_fn.lookup_type = 'ZA_EE_FUNCTION_TYPE' --Added for Bug 7360563
AND hl_cat.lookup_code <> '15'
AND hl_lev.lookup_code <> '15'
AND hl_fn.lookup_code <>'15'
AND hl_cat.application_id = '800'
AND hl_lev.application_id = '800'
AND hl_fn.application_id = '800'
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)
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)
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)
and nvl(paei.aei_information6, 'N') <> 'Y'
order BY paei.aei_information7, paaf.payroll_id;
select peevf.screen_entry_value screen_value,
months_between(least(peevf.effective_end_date ,p_report_date),greatest(peevf.effective_start_date-1 ,add_months(p_report_date,-12))) months_bet
from pay_element_entry_values_f peevf
,pay_element_entries_f peef
where peef.assignment_id = l_assignment_id
and peevf.element_entry_id = peef.element_entry_id
and peevf.input_value_id = l_input_id
and (add_months(p_report_date,-12)+1 <=peef.effective_end_date and p_report_date>=peef.effective_start_date)
and (add_months(p_report_date,-12)+1 <=peevf.effective_end_date and p_report_date>=peevf.effective_start_date)
and peef.effective_start_date between peevf.effective_start_date and peevf.effective_end_date;
g_assignments_table.delete;
select balance_type_id
into l_ee_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable Income'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_eea_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable Annual Income'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_er_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable ER Normal Contributions'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_era_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable ER Annual Contributions'
and legislation_code = 'ZA'
and business_group_id is null;
select pivf.input_value_id
into l_input_value_id
from pay_input_values_f pivf,
pay_element_types_f petf
where petf.element_name = 'ZA Employment Equity Remuneration'
and petf.business_group_id is null
and petf.legislation_code = 'ZA'
and p_report_date between petf.effective_start_date and petf.effective_end_date
and pivf.element_type_id = petf.element_type_id
and pivf.name = 'Remuneration'
and p_report_date between pivf.effective_start_date and pivf.effective_end_date;
Select per_information4, papf.sex
into l_race, l_sex
From per_all_people_f papf
Where papf.person_id = l_assignment.person_id
and p_report_date between papf.effective_start_date and papf.effective_end_date;
select ptpf.end_date + 1,
ptpl.end_date
into l_report_start,
l_report_end
from per_time_periods ptpf,
per_time_periods ptpl
where ptpl.payroll_id = l_assignment.payroll_id
and l_report_date between ptpl.start_date and ptpl.end_date
and ptpf.payroll_id = l_assignment.payroll_id
and add_months(l_report_date, -12) + 1 between ptpf.start_date and ptpf.end_date;
select ptpl.end_date
into l_report_end
from per_time_periods ptpl
where ptpl.payroll_id = l_assignment.payroll_id
and p_report_date between ptpl.start_date and ptpl.end_date;
select ptpt.number_per_fiscal_year
into l_period_frequency
from per_time_period_types ptpt,
pay_all_payrolls_f payr
where payr.payroll_id = l_assignment.payroll_id
and p_report_date between payr.effective_start_date and payr.effective_end_date
and ptpt.period_type = payr.period_type;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_ee_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
-- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_ee_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
-- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_ee_annual_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
-- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_eea_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
-- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_er_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_er_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_er_annual_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_era_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select input_value_id
into l_input_value_id
from per_pay_bases ppb
where ppb.pay_basis_id = l_assignment.pay_basis_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value)), 0)
into g_assignments_table(l_assignment.assignment_id).annual_income
from pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = l_input_value_id
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'ED1' -- no of employees in each categories
, p_report_date
, p_business_group_id
, g_cat_Enc_Diff_table(l_rowind).legal_entity_id
, haou.name
, null
, null
, g_cat_Enc_Diff_table(l_rowind).occupational_code_id
, g_cat_Enc_Diff_table(l_rowind).occupational_code
, g_cat_Enc_Diff_table(l_rowind).ma
, g_cat_Enc_Diff_table(l_rowind).mc
, g_cat_Enc_Diff_table(l_rowind).mi
, g_cat_Enc_Diff_table(l_rowind).mw
, g_cat_Enc_Diff_table(l_rowind).fa
, g_cat_Enc_Diff_table(l_rowind).fc
, g_cat_Enc_Diff_table(l_rowind).fi
, g_cat_Enc_Diff_table(l_rowind).fw
, g_cat_Enc_Diff_table(l_rowind).total
FROM hr_all_organization_units haou
Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'ED1I' -- income
, p_report_date
, p_business_group_id
, g_cat_Enc_Diff_table(l_rowind).legal_entity_id
, haou.name
, null
, null
, g_cat_Enc_Diff_table(l_rowind).occupational_code_id
, g_cat_Enc_Diff_table(l_rowind).occupational_code
, g_cat_Enc_Diff_table(l_rowind).ma_inc
, g_cat_Enc_Diff_table(l_rowind).mc_inc
, g_cat_Enc_Diff_table(l_rowind).mi_inc
, g_cat_Enc_Diff_table(l_rowind).mw_inc
, g_cat_Enc_Diff_table(l_rowind).fa_inc
, g_cat_Enc_Diff_table(l_rowind).fc_inc
, g_cat_Enc_Diff_table(l_rowind).fi_inc
, g_cat_Enc_Diff_table(l_rowind).fw_inc
, g_cat_Enc_Diff_table(l_rowind).total_inc
FROM hr_all_organization_units haou
Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'ED2' -- no of employees in each categories
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, null
, g_lev_Enc_Diff_table(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table(l_rowind).occupational_code
, g_lev_Enc_Diff_table(l_rowind).ma
, g_lev_Enc_Diff_table(l_rowind).mc
, g_lev_Enc_Diff_table(l_rowind).mi
, g_lev_Enc_Diff_table(l_rowind).mw
, g_lev_Enc_Diff_table(l_rowind).fa
, g_lev_Enc_Diff_table(l_rowind).fc
, g_lev_Enc_Diff_table(l_rowind).fi
, g_lev_Enc_Diff_table(l_rowind).fw
, g_lev_Enc_Diff_table(l_rowind).total
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'ED2I' -- income
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, null
, g_lev_Enc_Diff_table(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table(l_rowind).occupational_code
, g_lev_Enc_Diff_table(l_rowind).ma_inc
, g_lev_Enc_Diff_table(l_rowind).mc_inc
, g_lev_Enc_Diff_table(l_rowind).mi_inc
, g_lev_Enc_Diff_table(l_rowind).mw_inc
, g_lev_Enc_Diff_table(l_rowind).fa_inc
, g_lev_Enc_Diff_table(l_rowind).fc_inc
, g_lev_Enc_Diff_table(l_rowind).fi_inc
, g_lev_Enc_Diff_table(l_rowind).fw_inc
, g_lev_Enc_Diff_table(l_rowind).total_inc
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'ED1' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
Null disability,
Null employment_type,
hl.lookup_code level_cat_code,
hl.meaning ,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'ED1'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'ED1I' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
Null disability,
Null employment_type,
hl.lookup_code level_cat_code,
hl.meaning,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'ED1I'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'ED2' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
null disability,
null employment_type,
hl.lookup_code level_cat_code,
hl.meaning,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'ED2'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'ED2I' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
null disability,
null employment_type,
hl.lookup_code level_cat_code,
hl.meaning ,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'ED2I'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
g_new_assignments_table.delete;
g_cat_averages_table.delete;
g_lev_averages_table.delete;
g_cat_Enc_Diff_table.delete;
g_lev_Enc_Diff_table.delete;
g_lev_Enc_Diff_table_F.delete;
g_lev_Enc_Diff_table_T.delete;
g_lev_Enc_Diff_table_TF.delete;
DELETE FROM per_za_employment_equity
Where report_id IN
( 'ED', 'EDI', 'EDF','EDFI');
select paaf.assignment_id,
paaf.person_id, -- Bug 4413678
paaf.payroll_id,
paei.aei_information7 ,
hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
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,
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,
paaf.pay_basis_id
from per_assignment_extra_info paei,
per_assignment_status_types past,
per_all_assignments_f paaf,
hr_lookups hl_cat,
hr_lookups hl_lev,
hr_lookups hl_fn
where paaf.business_group_id = p_business_group_id
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and past.assignment_status_type_id = paaf.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
AND hl_cat.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
AND hl_lev.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
AND hl_fn.lookup_type = 'ZA_EE_FUNCTION_TYPE' --Added for Bug 7360563
AND hl_cat.lookup_code <> '15'
AND hl_lev.lookup_code <> '15'
AND hl_fn.lookup_code <>'15'
AND hl_cat.application_id = '800'
AND hl_lev.application_id = '800'
AND hl_fn.application_id = '800'
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)
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)
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)
and nvl(paei.aei_information6, 'N') <> 'Y'
order BY paei.aei_information7, paaf.payroll_id;
select paaf.assignment_id,
paaf.person_id, -- Bug 4413678
paaf.payroll_id,
paei.aei_information7 ,
hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
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,2009) occupational_level,
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,
paaf.pay_basis_id
from per_assignment_extra_info paei,
per_all_assignments_f paaf,
hr_lookups hl_lev,
hr_lookups hl_fn
where paaf.business_group_id = p_business_group_id
and (add_months(p_report_date,-12)+1 <=paaf.effective_end_date and p_report_date >=paaf.effective_start_date)
and paaf.effective_end_date = ( select max(paaf1.effective_end_date)
from per_all_assignments_f paaf1,
per_assignment_status_types past
where paaf1.assignment_id = paaf.assignment_id
and paaf1.effective_start_date <= p_report_date
and past.assignment_status_type_id = paaf1.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
)
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
AND hl_lev.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
AND hl_fn.lookup_type = 'ZA_EE_FUNCTION_TYPE' --Added for Bug 7360563
AND hl_lev.lookup_code <> '15'
AND hl_fn.lookup_code <>'15'
AND hl_lev.application_id = '800'
AND hl_fn.application_id = '800'
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,2009)
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,2009)
and nvl(paei.aei_information6, 'N') <> 'Y'
order BY paei.aei_information7, paaf.payroll_id;
select peevf.screen_entry_value screen_value,
months_between(least(peevf.effective_end_date ,p_report_date),greatest(peevf.effective_start_date-1 ,add_months(p_report_date,-12))) months_bet
from pay_element_entry_values_f peevf
,pay_element_entries_f peef
where peef.assignment_id = l_assignment_id
and peevf.element_entry_id = peef.element_entry_id
and peevf.input_value_id = l_input_id
and (add_months(p_report_date,-12)+1 <=peef.effective_end_date and p_report_date>=peef.effective_start_date)
and (add_months(p_report_date,-12)+1 <=peevf.effective_end_date and p_report_date>=peevf.effective_start_date)
and peef.effective_start_date between peevf.effective_start_date and peevf.effective_end_date;
g_new_assignments_table.delete;
select balance_type_id
into l_ee_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable Income'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_eea_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable Annual Income'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_er_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable ER Normal Contributions'
and legislation_code = 'ZA'
and business_group_id is null;
select balance_type_id
into l_era_balance_type_id
from pay_balance_types
where balance_name = 'Total Employment Equityable ER Annual Contributions'
and legislation_code = 'ZA'
and business_group_id is null;
select pivf.input_value_id
into l_input_value_id
from pay_input_values_f pivf,
pay_element_types_f petf
where petf.element_name = 'ZA Employment Equity Remuneration'
and petf.business_group_id is null
and petf.legislation_code = 'ZA'
and p_report_date between petf.effective_start_date and petf.effective_end_date
and pivf.element_type_id = petf.element_type_id
and pivf.name = 'Remuneration'
and p_report_date between pivf.effective_start_date and pivf.effective_end_date;
Select per_information4, papf.sex,
decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(substr(papf.PER_INFORMATION11,1,10),'/','')
-'19940427')
,-1,null,'F')))
into l_race, l_sex, l_type
From per_all_people_f papf
Where papf.person_id = l_assignment.person_id
and p_report_date between papf.effective_start_date and papf.effective_end_date;
select ptpf.end_date + 1,
ptpl.end_date
into l_report_start,
l_report_end
from per_time_periods ptpf,
per_time_periods ptpl
where ptpl.payroll_id = l_assignment.payroll_id
and l_report_date between ptpl.start_date and ptpl.end_date
and ptpf.payroll_id = l_assignment.payroll_id
and add_months(l_report_date, -12) + 1 between ptpf.start_date and ptpf.end_date;
select ptpl.end_date
into l_report_end
from per_time_periods ptpl
where ptpl.payroll_id = l_assignment.payroll_id
and p_report_date between ptpl.start_date and ptpl.end_date;
select ptpt.number_per_fiscal_year
into l_period_frequency
from per_time_period_types ptpt,
pay_all_payrolls_f payr
where payr.payroll_id = l_assignment.payroll_id
and p_report_date between payr.effective_start_date and payr.effective_end_date
and ptpt.period_type = payr.period_type;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_ee_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
-- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_ee_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
-- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_ee_annual_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
-- BUG 2665394 ADDED THE TABLE TO IMPROVE THE PERFORMANCE
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_eea_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
-- BUG 2665394 ADDED THE JOINS TO IMPROVE THE PERFORMANCE
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_er_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_er_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbff.scale), 0)
into l_er_annual_income
from pay_balance_feeds_f pbff,
pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg --Bug 4872110
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- Bug 8911880
-- and ppa.date_earned between l_report_start and l_report_end
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and pbff.balance_type_id = l_era_balance_type_id
and ppa.effective_date between pbff.effective_start_date and pbff.effective_end_date
and prrv.input_value_id = pbff.input_value_id
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
select input_value_id
into l_input_value_id
from per_pay_bases ppb
where ppb.pay_basis_id = l_assignment.pay_basis_id;
select pivff.input_value_id --Pay Value input value ID
into l_input_value_id2
from pay_input_values_f pivf, --Sal Basis Input Value
pay_input_values_f pivff --Pay Value Input Value
where pivf.input_value_id = l_input_value_id
and pivff.element_type_id = pivf.element_type_id
and pivff.name='Pay Value'
and p_report_date between pivf.effective_start_date and pivf.effective_end_date
and p_report_date between pivff.effective_start_date and pivff.effective_end_date;
select nvl(sum(fnd_number.canonical_to_number(prrv.result_value)), 0)
into g_new_assignments_table(l_assignment.assignment_id).annual_income
from pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f asg
where paa.assignment_id = l_assignment.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between add_months(p_report_date,-12)+1 and p_report_date
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = l_input_value_id2
and paa.assignment_id = asg.assignment_id
and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ppa.payroll_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'ED' -- no of employees in each categories
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Permanent'
, g_lev_Enc_Diff_table(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table(l_rowind).occupational_code
, g_lev_Enc_Diff_table(l_rowind).ma
, g_lev_Enc_Diff_table(l_rowind).mc
, g_lev_Enc_Diff_table(l_rowind).mi
, g_lev_Enc_Diff_table(l_rowind).mw
, g_lev_Enc_Diff_table(l_rowind).fa
, g_lev_Enc_Diff_table(l_rowind).fc
, g_lev_Enc_Diff_table(l_rowind).fi
, g_lev_Enc_Diff_table(l_rowind).fw
, g_lev_Enc_Diff_table(l_rowind).total
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'EDI' -- income
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Permanent'
, g_lev_Enc_Diff_table(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table(l_rowind).occupational_code
, g_lev_Enc_Diff_table(l_rowind).ma_inc
, g_lev_Enc_Diff_table(l_rowind).mc_inc
, g_lev_Enc_Diff_table(l_rowind).mi_inc
, g_lev_Enc_Diff_table(l_rowind).mw_inc
, g_lev_Enc_Diff_table(l_rowind).fa_inc
, g_lev_Enc_Diff_table(l_rowind).fc_inc
, g_lev_Enc_Diff_table(l_rowind).fi_inc
, g_lev_Enc_Diff_table(l_rowind).fw_inc
, g_lev_Enc_Diff_table(l_rowind).total_inc
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'EDF' -- no of employees in each categories
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table_F(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Permanent'
, g_lev_Enc_Diff_table_F(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table_F(l_rowind).occupational_code
, g_lev_Enc_Diff_table_F(l_rowind).ma
, g_lev_Enc_Diff_table_F(l_rowind).mc
, g_lev_Enc_Diff_table_F(l_rowind).mi
, g_lev_Enc_Diff_table_F(l_rowind).mw
, g_lev_Enc_Diff_table_F(l_rowind).fa
, g_lev_Enc_Diff_table_F(l_rowind).fc
, g_lev_Enc_Diff_table_F(l_rowind).fi
, g_lev_Enc_Diff_table_F(l_rowind).fw
, g_lev_Enc_Diff_table_F(l_rowind).total
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table_F(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'EDFI' -- income
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table_F(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Permanent'
, g_lev_Enc_Diff_table_F(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table_F(l_rowind).occupational_code
, g_lev_Enc_Diff_table_F(l_rowind).ma_inc
, g_lev_Enc_Diff_table_F(l_rowind).mc_inc
, g_lev_Enc_Diff_table_F(l_rowind).mi_inc
, g_lev_Enc_Diff_table_F(l_rowind).mw_inc
, g_lev_Enc_Diff_table_F(l_rowind).fa_inc
, g_lev_Enc_Diff_table_F(l_rowind).fc_inc
, g_lev_Enc_Diff_table_F(l_rowind).fi_inc
, g_lev_Enc_Diff_table_F(l_rowind).fw_inc
, g_lev_Enc_Diff_table_F(l_rowind).total_inc
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table_F(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'ED' -- no of employees in each categories
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table_T(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Non-Permanent'
, g_lev_Enc_Diff_table_T(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table_T(l_rowind).occupational_code
, g_lev_Enc_Diff_table_T(l_rowind).ma
, g_lev_Enc_Diff_table_T(l_rowind).mc
, g_lev_Enc_Diff_table_T(l_rowind).mi
, g_lev_Enc_Diff_table_T(l_rowind).mw
, g_lev_Enc_Diff_table_T(l_rowind).fa
, g_lev_Enc_Diff_table_T(l_rowind).fc
, g_lev_Enc_Diff_table_T(l_rowind).fi
, g_lev_Enc_Diff_table_T(l_rowind).fw
, g_lev_Enc_Diff_table_T(l_rowind).total
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table_T(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'EDI' -- income
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table_T(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Non-Permanent'
, g_lev_Enc_Diff_table_T(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table_T(l_rowind).occupational_code
, g_lev_Enc_Diff_table_T(l_rowind).ma_inc
, g_lev_Enc_Diff_table_T(l_rowind).mc_inc
, g_lev_Enc_Diff_table_T(l_rowind).mi_inc
, g_lev_Enc_Diff_table_T(l_rowind).mw_inc
, g_lev_Enc_Diff_table_T(l_rowind).fa_inc
, g_lev_Enc_Diff_table_T(l_rowind).fc_inc
, g_lev_Enc_Diff_table_T(l_rowind).fi_inc
, g_lev_Enc_Diff_table_T(l_rowind).fw_inc
, g_lev_Enc_Diff_table_T(l_rowind).total_inc
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table_T(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'EDF' -- no of employees in each categories
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table_TF(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Non-Permanent'
, g_lev_Enc_Diff_table_TF(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table_TF(l_rowind).occupational_code
, g_lev_Enc_Diff_table_TF(l_rowind).ma
, g_lev_Enc_Diff_table_TF(l_rowind).mc
, g_lev_Enc_Diff_table_TF(l_rowind).mi
, g_lev_Enc_Diff_table_TF(l_rowind).mw
, g_lev_Enc_Diff_table_TF(l_rowind).fa
, g_lev_Enc_Diff_table_TF(l_rowind).fc
, g_lev_Enc_Diff_table_TF(l_rowind).fi
, g_lev_Enc_Diff_table_TF(l_rowind).fw
, g_lev_Enc_Diff_table_TF(l_rowind).total
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table_TF(l_rowind).legal_entity_id;
INSERT INTO per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
Select 'EDFI' -- income
, p_report_date
, p_business_group_id
, g_lev_Enc_Diff_table_TF(l_rowind).legal_entity_id
, haou.name legal_entity
, null
, 'Non-Permanent'
, g_lev_Enc_Diff_table_TF(l_rowind).occupational_code_id
, g_lev_Enc_Diff_table_TF(l_rowind).occupational_code
, g_lev_Enc_Diff_table_TF(l_rowind).ma_inc
, g_lev_Enc_Diff_table_TF(l_rowind).mc_inc
, g_lev_Enc_Diff_table_TF(l_rowind).mi_inc
, g_lev_Enc_Diff_table_TF(l_rowind).mw_inc
, g_lev_Enc_Diff_table_TF(l_rowind).fa_inc
, g_lev_Enc_Diff_table_TF(l_rowind).fc_inc
, g_lev_Enc_Diff_table_TF(l_rowind).fi_inc
, g_lev_Enc_Diff_table_TF(l_rowind).fw_inc
, g_lev_Enc_Diff_table_TF(l_rowind).total_inc
FROM hr_all_organization_units haou
Where haou.organization_id = g_lev_Enc_Diff_table_TF(l_rowind).legal_entity_id;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'ED' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
null disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'ED'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EDI' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
null disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning ,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EDI'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EDF' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
null disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EDF'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EDFI' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
null disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning ,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EDFI'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select decode(report_id,'ED','EDF','EDFI'),
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
0,
0,
0,
0,
0,
0,
0,
0,
0
from per_za_employment_equity pzee1
Where pzee1.business_group_id = p_business_group_id
AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
AND pzee1.report_id IN ('ED','EDI')
AND not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.business_group_id = pzee1.business_group_id
AND pzee.legal_entity_id = pzee1.legal_entity_id
AND ( pzee1.report_id ||'F' = pzee.report_id --row not present for ED
OR
substr(pzee1.report_id,1,2)||'FI' = pzee.report_id)
AND pzee1.level_cat_code = pzee.level_cat_code
AND pzee1.level_cat = pzee.level_cat
and pzee.employment_type = pzee1.employment_type
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select decode(report_id,'EDF','ED','EDI'),
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
0,
0,
0,
0,
0,
0,
0,
0,
0
from per_za_employment_equity pzee1
Where pzee1.business_group_id = p_business_group_id
AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
AND pzee1.report_id IN ('EDF','EDFI')
AND not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.business_group_id = pzee1.business_group_id
AND pzee.legal_entity_id = pzee1.legal_entity_id
AND ( pzee.report_id ||'F' = pzee1.report_id --row not present for ED
OR
(pzee1.report_id='EDFI' and pzee.report_id='EDI'))
AND pzee1.level_cat_code = pzee.level_cat_code
AND pzee1.level_cat = pzee.level_cat
and pzee.employment_type = pzee1.employment_type
);
select pucifcat.value
into l_termination_reason
from pay_user_column_instances_f pucifcat,
pay_user_rows_f purfcat,
pay_user_columns puccat,
pay_user_rows_f purfqc,
pay_user_column_instances_f pucifqc,
pay_user_columns pucqc,
pay_user_tables put
where put.user_table_name = 'ZA_TERMINATION_CATEGORIES'
and put.business_group_id is null
and put.legislation_code = 'ZA'
and pucqc.user_table_id = put.user_table_id
and pucqc.user_column_name = 'Lookup Code'
and pucifqc.user_column_id = pucqc.user_column_id
and pucifqc.business_group_id = p_business_group_id
and p_report_date between pucifqc.effective_start_date and pucifqc.effective_end_date
and pucifqc.value = p_reason_code
and purfqc.user_table_id = put.user_table_id
and purfqc.user_row_id = pucifqc.user_row_id
and purfqc.business_group_id = pucifqc.business_group_id
and p_report_date between purfqc.effective_start_date and purfqc.effective_end_date
and puccat.user_table_id = put.user_table_id
and puccat.user_column_name = 'Termination Category'
and purfcat.user_table_id = put.user_table_id
and purfcat.business_group_id = pucifqc.business_group_id
and p_report_date between purfcat.effective_start_date and purfcat.effective_end_date
and purfcat.row_low_range_or_name = purfqc.row_low_range_or_name
and pucifcat.user_column_id = puccat.user_column_id
and pucifcat.user_row_id = purfcat.user_row_id
and p_report_date between pucifcat.effective_start_date and pucifcat.effective_end_date
and pucifcat.business_group_id = pucifqc.business_group_id
and pucifcat.value in
(
'Resignation',
'Non-Renewal of Contract',
'Dismissal - Operational Requirements',
'Dismissal - Misconduct',
'Dismissal - Incapacity',
--'Other'
'Retirement',
'Death'
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
ma,
mc,
mi,
mw,
fa,
fc,
fi,
fw,
total
)
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and p_report_date between papf.effective_start_date and papf.effective_end_date
and papf.current_employee_flag = 'Y'
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
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)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code <> '15' -- Not Applicable.
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)
and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
AND hl2.lookup_code <> '15' -- Not Applicable.
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)
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
hl.lookup_code,
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'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ1' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ1'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ1F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ1F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,2009), 'No Occupational Level') occupational_level,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and p_report_date between papf.effective_start_date and papf.effective_end_date
and papf.current_employee_flag = 'Y'
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id,paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code <> '15' -- Operation / core function
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
hl.lookup_code,
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,2009), 'No Occupational Level'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F')));
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ2' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ2'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ2F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ2F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,2009), 'No Occupational Level') occupational_level,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and p_report_date between papf.effective_start_date and papf.effective_end_date
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code = '1' -- Operation / core function
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
hl.lookup_code,
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,2009), 'No Occupational Level'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F')))
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ3' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ3'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ3F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ3F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,2009), 'No Occupational Level') occupational_level,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and p_report_date between papf.effective_start_date and papf.effective_end_date
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and p_report_date between paaf.effective_start_date and paaf.effective_end_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
AND hl1.lookup_code = '2' -- Support function
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
hl.lookup_code,
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,2009), 'No Occupational Level'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F')))
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ4' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ4'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ4F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ4F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,2009), 'No Occupational Level') occupational_level,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
and ppos.date_start between add_months(p_report_date, -12) + 1 and p_report_date
and papf.effective_start_date = ppos.date_start
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_start_date = ppos.date_start
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable.
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
hl.lookup_code,
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,2009), 'No Occupational Level'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F')))
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ5' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ5'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ5F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ5F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
- to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
hl.lookup_code lookup_code,
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,2009), 'No Occupational Level') occupational_level,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY')) > add_months(p_report_date, -12) + 1
and ppos.date_start < p_report_date
and papf.effective_start_date = ppos.date_start
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_start_date between ppos.date_start and nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paaf.effective_start_date > add_months(p_report_date, -12) + 1
and paaf.effective_start_date <= p_report_date
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable.
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- AND hl2.lookup_code <> '15' -- Not Applicable.
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,2009)), '9999999999') <
any
(
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,2009)) lookup_code
from per_all_assignments_f paaf1
where paaf1.person_id = papf.person_id
and paaf1.primary_flag = 'Y'
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,2009)) is not null
and paaf1.effective_end_date + 1 = paaf.effective_start_date
)
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
hl.lookup_code,
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,2009), 'No Occupational Level'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))) ;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ6' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ6'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ6F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ6F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
hl.lookup_code meaning_code,
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,2009), 'No Occupational Level') occupational_level,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
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
and papf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
AND hl.lookup_code <> '15' -- Not Applicable.
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- AND hl2.lookup_code <> '15' -- Not Applicable.
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
hl.lookup_code,
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,2009), 'No Occupational Level'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
)
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
tpa.meaning_code,
tpa.occupational_level;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ7' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ7'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ7F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
hl.lookup_code level_cat_code,
hl.meaning level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_lookups hl
, hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat_code = hl.lookup_code
and pzee.report_id = 'EQ7F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
decode
(
tpa.termination_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
--'Other', 6,
'Retirement', 6,
'Death', 7,
null
) meaning_code,
/*decode
(
tpa.termination_reason,
'Dismissal - Operational Requirements', 'retrenchment -Operational requirements',
tpa.termination_reason
),*/
tpa.termination_reason,
sum(tpa.male_african) MA,
sum(tpa.male_coloured) MC,
sum(tpa.male_indian) MI,
sum(tpa.male_white) MW,
sum(tpa.female_african) FA,
sum(tpa.female_coloured) FC,
sum(tpa.female_indian) FI,
sum(tpa.female_white) FW,
sum(tpa.total) total
from
(
select p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
) report_code,
p_report_date reporting_date,
paaf.business_group_id,
paei.aei_information7 legal_entity_id,
haou.name legal_entity,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
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
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
ppos.leaving_reason meaning_code,
nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) female_african,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) female_coloured,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) female_indian,
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) female_white,
sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '03', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '01', 1, 0), 0)) +
sum(decode(papf.sex, 'F', decode(papf.per_information4, '04', 1, 0), 0)) total
from hr_lookups hl,
hr_lookups hl1,
-- hr_lookups hl2,
hr_all_organization_units haou,
per_assignment_extra_info paei,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_all_people_f papf
where papf.business_group_id = p_business_group_id
and papf.current_employee_flag = 'Y'
and ppos.person_id = papf.person_id
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
and papf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paaf.person_id = papf.person_id
and paaf.primary_flag = 'Y'
and paaf.effective_end_date = nvl(ppos.actual_termination_date, to_date('31-12-4712', 'DD-MM-YYYY'))
and paei.assignment_id = paaf.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
and paei.aei_information7 is not null
and nvl(paei.aei_information6, 'N') <> 'Y'
and haou.organization_id = paei.aei_information7
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'
and nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
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,2009)
and hl.lookup_code <> '15' -- Not Applicable
and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
and hl1.lookup_code <> '15' -- Not Applicable
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,2009)
-- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
-- 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)
-- and hl2.lookup_code <> '15' -- Not Applicable
group by paaf.business_group_id,
paei.aei_information7,
haou.name,
decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
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)),
-- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
ppos.leaving_reason,
nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
decode(papf.PER_INFORMATION11,null,null,
decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
-to_char(l_nat_date,'YYYYMMDD'))
,-1,null,'F'))
)
) tpa
group by tpa.report_code,
tpa.reporting_date,
tpa.business_group_id,
tpa.legal_entity_id,
tpa.legal_entity,
tpa.disability,
tpa.employment_type,
-- tpa.meaning_code,
decode
(
tpa.termination_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
--'Other', 6,
'Retirement', 6,
'Death', 7,
null
) ,
tpa.termination_reason;
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ8' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
decode
(
l_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
--'Other', 6,
'Retirement', 6,
'Death', 7,
null
) level_cat_code,
/*decode
(
l_reason,
'Dismissal - Operational Requirements', 'retrenchment -Operational requirements',
l_reason
) level_cat,*/
l_reason level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat = l_reason
and pzee.report_id = 'EQ8'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select 'EQ8F' report_id,
p_report_date reporting_date,
p_business_group_id business_group_id,
haou.organization_id legal_entity_id,
haou.name legal_entity,
'Y' disability,
'Permanent' employment_type,
decode
(
l_reason,
'Resignation', 1,
'Non-Renewal of Contract', 2,
'Dismissal - Operational Requirements', 3,
'Dismissal - Misconduct', 4,
'Dismissal - Incapacity', 5,
'Other', 6,
null
) level_cat_code,
/*decode
(
l_reason,
'Dismissal - Operational Requirements', 'Dismissal - Operational Requirements (Retrenchment)',
l_reason
) level_cat,*/
l_reason level_cat,
0 MA,
0 MC,
0 MI,
0 MW,
0 FA,
0 FC,
0 FI,
0 FW,
0 total
from hr_all_organization_units haou
where not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.level_cat = l_reason
and pzee.report_id = 'EQ8F'
and pzee.business_group_id = p_business_group_id --Bug 4872110
and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)
and pzee.disability = 'Y'
and pzee.employment_type = 'Permanent'
)
and haou.business_group_id = p_business_group_id --Bug 4872110
and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
DELETE FROM per_za_employment_equity
Where REPORT_ID IN ('EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8',
'EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F'
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select substr(report_id,1,3),
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
0,
0,
0,
0,
0,
0,
0,
0,
0
from per_za_employment_equity pzee1
Where pzee1.business_group_id = p_business_group_id
AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
AND not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
AND pzee.legal_entity_id = pzee1.legal_entity_id
AND pzee.report_id ||'F' = pzee1.report_id
AND pzee1.level_cat_code = pzee.level_cat_code
AND pzee1.level_cat = pzee.level_cat
and nvl(pzee.disability,'X') = nvl(pzee1.disability,'X')
and pzee.employment_type = pzee1.employment_type
);
insert into per_za_employment_equity
(
report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
MA,
MC,
MI,
MW,
FA,
FC,
FI,
FW,
total
)
select report_id||'F' report_id,
reporting_date,
business_group_id,
legal_entity_id,
legal_entity,
disability,
employment_type,
level_cat_code,
level_cat,
0,
0,
0,
0,
0,
0,
0,
0,
0
from per_za_employment_equity pzee1
Where pzee1.business_group_id = p_business_group_id
AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
AND not exists
(
select 'X'
from per_za_employment_equity pzee
where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
AND pzee.legal_entity_id = pzee1.legal_entity_id
AND pzee1.report_id ||'F' = pzee.report_id
AND pzee1.level_cat_code = pzee.level_cat_code
AND pzee1.level_cat = pzee.level_cat
and nvl(pzee.disability,'X') = nvl(pzee1.disability,'X')
and pzee.employment_type = pzee1.employment_type
);