eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_ZA_EMPLOYMENT_EQUITY_PKG dependencies on PER_ZA_EMPLOYMENT_EQUITY

Line 1: PACKAGE BODY PER_ZA_EMPLOYMENT_EQUITY_PKG as

1: PACKAGE BODY PER_ZA_EMPLOYMENT_EQUITY_PKG as
2: /* $Header: perzaeer.pkb 120.45.12020000.2 2012/10/15 09:24:19 abdash ship $ */
3: /*
4: ==============================================================================
5: This package loads data into table per_za_employment_equity for use by

Line 5: This package loads data into table per_za_employment_equity for use by

1: PACKAGE BODY PER_ZA_EMPLOYMENT_EQUITY_PKG as
2: /* $Header: perzaeer.pkb 120.45.12020000.2 2012/10/15 09:24:19 abdash ship $ */
3: /*
4: ==============================================================================
5: This package loads data into table per_za_employment_equity for use by
6: the Employment Equity Reports
7:
8: MODIFICATION HISTORY
9:

Line 176: g_package constant varchar2(30) := 'per_za_employment_equity_pkg.';

172:
173:
174:
175: -- Global variables
176: g_package constant varchar2(30) := 'per_za_employment_equity_pkg.';
177: g_assignments_table t_assignments;
178: g_cat_averages_table t_averages;
179: g_lev_averages_table t_averages;
180:

Line 568: DELETE FROM per_za_employment_equity

564: g_cat_averages_table.delete;
565: g_lev_averages_table.delete;
566: g_cat_Enc_Diff_table.delete;
567: g_lev_Enc_Diff_table.delete;
568: DELETE FROM per_za_employment_equity
569: Where report_id IN
570: ( 'ED1', 'ED2', 'ED1I','ED2I');
571:
572: end reset_tables;

Line 715: 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,

711: select paaf.assignment_id,
712: paaf.person_id, -- Bug 4413678
713: paaf.payroll_id,
714: paei.aei_information7,
715: 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,
716: 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,
717: paaf.pay_basis_id
718: from per_assignment_extra_info paei,
719: per_assignment_status_types past,

Line 716: 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,

712: paaf.person_id, -- Bug 4413678
713: paaf.payroll_id,
714: paei.aei_information7,
715: 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,
716: 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,
717: paaf.pay_basis_id
718: from per_assignment_extra_info paei,
719: per_assignment_status_types past,
720: per_all_assignments_f paaf

Line 1819: insert into per_za_employment_equity

1815: if p_report_code = 'EQ1' then
1816:
1817: -- Note: The date effective select on per_all_assignments_f is ok in this case, since an assignment
1818: -- record always exist at the same time as an employee record with status EMP
1819: insert into per_za_employment_equity
1820: (
1821: report_id,
1822: reporting_date,
1823: business_group_id,

Line 1851: 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

1847: paaf.business_group_id,
1848: paei.aei_information7 legal_entity_id,
1849: haou.name legal_entity,
1850: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1851: 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
1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1853: hl.lookup_code meaning_code,
1854: 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,
1855: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

1848: paei.aei_information7 legal_entity_id,
1849: haou.name legal_entity,
1850: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1851: 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
1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1853: hl.lookup_code meaning_code,
1854: 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,
1855: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1856: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 1854: 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,

1850: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1851: 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
1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1853: hl.lookup_code meaning_code,
1854: 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,
1855: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1856: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
1857: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
1858: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 1891: 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)

1887: and paei.aei_information7 is not null
1888: and nvl(paei.aei_information6, 'N') <> 'Y'
1889: and haou.organization_id = paei.aei_information7
1890: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
1891: 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)
1892: AND hl.lookup_code <> '15' -- Not Applicable.
1893: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1894: AND hl1.lookup_code <> '15' -- Not Applicable.
1895: 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)

Line 1895: 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)

1891: 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)
1892: AND hl.lookup_code <> '15' -- Not Applicable.
1893: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1894: AND hl1.lookup_code <> '15' -- Not Applicable.
1895: 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)
1896: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1897: AND hl2.lookup_code <> '15' -- Not Applicable.
1898: 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)
1899: group by paaf.business_group_id,

Line 1898: 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)

1894: AND hl1.lookup_code <> '15' -- Not Applicable.
1895: 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)
1896: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1897: AND hl2.lookup_code <> '15' -- Not Applicable.
1898: 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)
1899: group by paaf.business_group_id,
1900: paei.aei_information7,
1901: haou.name,
1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073

Line 1903: 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

1899: group by paaf.business_group_id,
1900: paei.aei_information7,
1901: haou.name,
1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1903: 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
1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1905: hl.lookup_code,
1906: 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'),
1907: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073

1900: paei.aei_information7,
1901: haou.name,
1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1903: 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
1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1905: hl.lookup_code,
1906: 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'),
1907: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1908: decode(papf.PER_INFORMATION3,null,null,

Line 1906: 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'),

1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1903: 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
1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1905: hl.lookup_code,
1906: 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'),
1907: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1908: decode(papf.PER_INFORMATION3,null,null,
1909: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
1910: -to_char(p_report_date,'YYYYMMDD'))

Line 1917: insert into per_za_employment_equity

1913:
1914: commit;
1915:
1916: -- Inserts non-associated occupational categories with zero values
1917: insert into per_za_employment_equity
1918: (
1919: report_id,
1920: reporting_date,
1921: business_group_id,

Line 1961: from per_za_employment_equity pzee

1957: , hr_all_organization_units haou
1958: where not exists
1959: (
1960: select 'X'
1961: from per_za_employment_equity pzee
1962: where pzee.level_cat_code = hl.lookup_code
1963: and pzee.report_id = 'EQ1'
1964: and pzee.business_group_id = p_business_group_id --Bug 4872110
1965: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 1974: insert into per_za_employment_equity

1970: and haou.business_group_id = p_business_group_id --Bug 4872110
1971: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
1972:
1973: -- Inseting 0 VALUES FOR FOREIGN nationals
1974: insert into per_za_employment_equity
1975: (
1976: report_id,
1977: reporting_date,
1978: business_group_id,

Line 2018: from per_za_employment_equity pzee

2014: , hr_all_organization_units haou
2015: where not exists
2016: (
2017: select 'X'
2018: from per_za_employment_equity pzee
2019: where pzee.level_cat_code = hl.lookup_code
2020: and pzee.report_id = 'EQ1F'
2021: and pzee.business_group_id = p_business_group_id --Bug 4872110
2022: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2038: insert into per_za_employment_equity

2034: -- 5. Occupational Levels (only employees with disabilities)
2035: elsif p_report_code = 'EQ2' then
2036:
2037: -- Populate with Occupational Level Totals
2038: insert into per_za_employment_equity
2039: (
2040: report_id,
2041: reporting_date,
2042: business_group_id,

Line 2070: 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

2066: paaf.business_group_id,
2067: paei.aei_information7 legal_entity_id,
2068: haou.name legal_entity,
2069: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2070: 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
2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2072: hl.lookup_code meaning_code,
2073: 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,
2074: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

2067: paei.aei_information7 legal_entity_id,
2068: haou.name legal_entity,
2069: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2070: 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
2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2072: hl.lookup_code meaning_code,
2073: 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,
2074: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2075: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2073: 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,

2069: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2070: 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
2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2072: hl.lookup_code meaning_code,
2073: 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,
2074: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2075: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2076: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2077: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2110: 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)

2106: and paei.aei_information7 is not null
2107: and nvl(paei.aei_information6, 'N') <> 'Y'
2108: and haou.organization_id = paei.aei_information7
2109: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2110: 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)
2111: AND hl.lookup_code <> '15' -- Not Applicable.
2112: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2113: AND hl1.lookup_code <> '15' -- Operation / core function
2114: 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)

Line 2114: 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)

2110: 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)
2111: AND hl.lookup_code <> '15' -- Not Applicable.
2112: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2113: AND hl1.lookup_code <> '15' -- Operation / core function
2114: 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)
2115: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2116: 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)
2117: AND hl2.lookup_code <> '15' -- Not Applicable.
2118: group by paaf.business_group_id,

Line 2116: 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)

2112: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2113: AND hl1.lookup_code <> '15' -- Operation / core function
2114: 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)
2115: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2116: 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)
2117: AND hl2.lookup_code <> '15' -- Not Applicable.
2118: group by paaf.business_group_id,
2119: paei.aei_information7,
2120: haou.name,

Line 2122: 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

2118: group by paaf.business_group_id,
2119: paei.aei_information7,
2120: haou.name,
2121: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2122: 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
2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2124: hl.lookup_code,
2125: 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'),
2126: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073

2119: paei.aei_information7,
2120: haou.name,
2121: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2122: 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
2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2124: hl.lookup_code,
2125: 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'),
2126: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2127: decode(papf.PER_INFORMATION3,null,null,

Line 2125: 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'),

2121: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2122: 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
2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2124: hl.lookup_code,
2125: 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'),
2126: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2127: decode(papf.PER_INFORMATION3,null,null,
2128: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2129: -to_char(p_report_date,'YYYYMMDD'))

Line 2135: insert into per_za_employment_equity

2131:
2132: commit;
2133:
2134: -- Inserts non-associated occupational levels with zero values
2135: insert into per_za_employment_equity
2136: (
2137: report_id,
2138: reporting_date,
2139: business_group_id,

Line 2179: from per_za_employment_equity pzee

2175: , hr_all_organization_units haou
2176: where not exists
2177: (
2178: select 'X'
2179: from per_za_employment_equity pzee
2180: where pzee.level_cat_code = hl.lookup_code
2181: and pzee.report_id = 'EQ2'
2182: and pzee.business_group_id = p_business_group_id --Bug 4872110
2183: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2193: insert into per_za_employment_equity

2189: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2190:
2191:
2192: -- inserting 0 values for the Foreign Nationals
2193: insert into per_za_employment_equity
2194: (
2195: report_id,
2196: reporting_date,
2197: business_group_id,

Line 2237: from per_za_employment_equity pzee

2233: , hr_all_organization_units haou
2234: where not exists
2235: (
2236: select 'X'
2237: from per_za_employment_equity pzee
2238: where pzee.level_cat_code = hl.lookup_code
2239: and pzee.report_id = 'EQ2F'
2240: and pzee.business_group_id = p_business_group_id --Bug 4872110
2241: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2256: insert into per_za_employment_equity

2252: -- For employment equity enhancement
2253: elsif p_report_code = 'EQ3' then
2254:
2255: -- Populate with New Hires
2256: insert into per_za_employment_equity
2257: (
2258: report_id,
2259: reporting_date,
2260: business_group_id,

Line 2307: 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

2303: paaf.business_group_id,
2304: paei.aei_information7 legal_entity_id,
2305: haou.name legal_entity,
2306: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2307: 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
2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2309: hl.lookup_code meaning_code,
2310: 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,
2311: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073

2304: paei.aei_information7 legal_entity_id,
2305: haou.name legal_entity,
2306: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2307: 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
2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2309: hl.lookup_code meaning_code,
2310: 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,
2311: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2312: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2310: 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,

2306: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2307: 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
2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2309: hl.lookup_code meaning_code,
2310: 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,
2311: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2312: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2313: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2314: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2347: 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)

2343: and paei.aei_information7 is not null
2344: and nvl(paei.aei_information6, 'N') <> 'Y'
2345: and haou.organization_id = paei.aei_information7
2346: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2347: 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)
2348: AND hl.lookup_code <> '15' -- Not Applicable.
2349: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2350: AND hl1.lookup_code = '1' -- Operation / core function
2351: 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)

Line 2351: 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)

2347: 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)
2348: AND hl.lookup_code <> '15' -- Not Applicable.
2349: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2350: AND hl1.lookup_code = '1' -- Operation / core function
2351: 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)
2352: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2353: 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)
2354: AND hl2.lookup_code <> '15' -- Not Applicable.
2355: group by paaf.business_group_id,

Line 2353: 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)

2349: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2350: AND hl1.lookup_code = '1' -- Operation / core function
2351: 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)
2352: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2353: 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)
2354: AND hl2.lookup_code <> '15' -- Not Applicable.
2355: group by paaf.business_group_id,
2356: paei.aei_information7,
2357: haou.name,

Line 2359: 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)),

2355: group by paaf.business_group_id,
2356: paei.aei_information7,
2357: haou.name,
2358: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2359: 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)),
2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2361: hl.lookup_code,
2362: 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'),
2363: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),

2356: paei.aei_information7,
2357: haou.name,
2358: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2359: 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)),
2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2361: hl.lookup_code,
2362: 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'),
2363: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2364: decode(papf.PER_INFORMATION3,null,null,

Line 2362: 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'),

2358: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2359: 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)),
2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2361: hl.lookup_code,
2362: 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'),
2363: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2364: decode(papf.PER_INFORMATION3,null,null,
2365: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2366: -to_char(p_report_date,'YYYYMMDD'))

Line 2382: insert into per_za_employment_equity

2378:
2379: commit;
2380:
2381: -- Inserts non-associated occupational levels with zero values
2382: insert into per_za_employment_equity
2383: (
2384: report_id,
2385: reporting_date,
2386: business_group_id,

Line 2426: from per_za_employment_equity pzee

2422: , hr_all_organization_units haou
2423: where not exists
2424: (
2425: select 'X'
2426: from per_za_employment_equity pzee
2427: where pzee.level_cat_code = hl.lookup_code
2428: and pzee.report_id = 'EQ3'
2429: and pzee.business_group_id = p_business_group_id --Bug 4872110
2430: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2439: insert into per_za_employment_equity

2435: and haou.business_group_id = p_business_group_id --Bug 4872110
2436: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2437:
2438: -- Inserts non-associated occupational levels with zero values
2439: insert into per_za_employment_equity
2440: (
2441: report_id,
2442: reporting_date,
2443: business_group_id,

Line 2483: from per_za_employment_equity pzee

2479: , hr_all_organization_units haou
2480: where not exists
2481: (
2482: select 'X'
2483: from per_za_employment_equity pzee
2484: where pzee.level_cat_code = hl.lookup_code
2485: and pzee.report_id = 'EQ3F'
2486: and pzee.business_group_id = p_business_group_id --Bug 4872110
2487: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2503: insert into per_za_employment_equity

2499: -- the twelve months preceding this report)
2500: elsif p_report_code = 'EQ4' then
2501:
2502: -- Populate with New Hires
2503: insert into per_za_employment_equity
2504: (
2505: report_id,
2506: reporting_date,
2507: business_group_id,

Line 2554: 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

2550: paaf.business_group_id,
2551: paei.aei_information7 legal_entity_id,
2552: haou.name legal_entity,
2553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2554: 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
2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2556: hl.lookup_code meaning_code,
2557: 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,
2558: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073

2551: paei.aei_information7 legal_entity_id,
2552: haou.name legal_entity,
2553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2554: 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
2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2556: hl.lookup_code meaning_code,
2557: 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,
2558: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2557: 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,

2553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2554: 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
2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2556: hl.lookup_code meaning_code,
2557: 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,
2558: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2560: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2561: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2594: 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)

2590: and paei.aei_information7 is not null
2591: and nvl(paei.aei_information6, 'N') <> 'Y'
2592: and haou.organization_id = paei.aei_information7
2593: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2594: 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)
2595: AND hl.lookup_code <> '15' -- Not Applicable.
2596: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2597: AND hl1.lookup_code = '2' -- Support function
2598: 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)

Line 2598: 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)

2594: 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)
2595: AND hl.lookup_code <> '15' -- Not Applicable.
2596: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2597: AND hl1.lookup_code = '2' -- Support function
2598: 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)
2599: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2600: 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)
2601: AND hl2.lookup_code <> '15' -- Not Applicable.
2602: group by paaf.business_group_id,

Line 2600: 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)

2596: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2597: AND hl1.lookup_code = '2' -- Support function
2598: 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)
2599: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2600: 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)
2601: AND hl2.lookup_code <> '15' -- Not Applicable.
2602: group by paaf.business_group_id,
2603: paei.aei_information7,
2604: haou.name,

Line 2606: 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)),

2602: group by paaf.business_group_id,
2603: paei.aei_information7,
2604: haou.name,
2605: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2606: 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)),
2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2608: hl.lookup_code,
2609: 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'),
2610: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),

2603: paei.aei_information7,
2604: haou.name,
2605: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2606: 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)),
2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2608: hl.lookup_code,
2609: 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'),
2610: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2611: decode(papf.PER_INFORMATION3,null,null,

Line 2609: 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'),

2605: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2606: 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)),
2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2608: hl.lookup_code,
2609: 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'),
2610: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2611: decode(papf.PER_INFORMATION3,null,null,
2612: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2613: -to_char(p_report_date,'YYYYMMDD'))

Line 2629: insert into per_za_employment_equity

2625:
2626: commit;
2627:
2628: -- Inserts non-associated occupational levels with zero values
2629: insert into per_za_employment_equity
2630: (
2631: report_id,
2632: reporting_date,
2633: business_group_id,

Line 2673: from per_za_employment_equity pzee

2669: , hr_all_organization_units haou
2670: where not exists
2671: (
2672: select 'X'
2673: from per_za_employment_equity pzee
2674: where pzee.level_cat_code = hl.lookup_code
2675: and pzee.report_id = 'EQ4'
2676: and pzee.business_group_id = p_business_group_id --Bug 4872110
2677: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2686: insert into per_za_employment_equity

2682: and haou.business_group_id = p_business_group_id --Bug 4872110
2683: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2684:
2685: -- Inserts non-associated occupational levels with zero values
2686: insert into per_za_employment_equity
2687: (
2688: report_id,
2689: reporting_date,
2690: business_group_id,

Line 2730: from per_za_employment_equity pzee

2726: , hr_all_organization_units haou
2727: where not exists
2728: (
2729: select 'X'
2730: from per_za_employment_equity pzee
2731: where pzee.level_cat_code = hl.lookup_code
2732: and pzee.report_id = 'EQ4F'
2733: and pzee.business_group_id = p_business_group_id --Bug 4872110
2734: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2753: insert into per_za_employment_equity

2749: -- the twelve months preceding this report)
2750: elsif p_report_code = 'EQ5' then
2751:
2752: -- Populate with New Hires
2753: insert into per_za_employment_equity
2754: (
2755: report_id,
2756: reporting_date,
2757: business_group_id,

Line 2804: 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

2800: paaf.business_group_id,
2801: paei.aei_information7 legal_entity_id,
2802: haou.name legal_entity,
2803: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2804: 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
2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2806: hl.lookup_code meaning_code,
2807: 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,
2808: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073

2801: paei.aei_information7 legal_entity_id,
2802: haou.name legal_entity,
2803: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2804: 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
2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2806: hl.lookup_code meaning_code,
2807: 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,
2808: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2809: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2807: 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,

2803: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2804: 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
2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2806: hl.lookup_code meaning_code,
2807: 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,
2808: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2809: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2810: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2811: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2847: 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)

2843: and paei.aei_information7 is not null
2844: and nvl(paei.aei_information6, 'N') <> 'Y'
2845: and haou.organization_id = paei.aei_information7
2846: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2847: 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)
2848: AND hl.lookup_code <> '15' -- Not Applicable.
2849: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2850: and hl1.lookup_code <> '15' -- Not Applicable.
2851: 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)

Line 2851: 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)

2847: 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)
2848: AND hl.lookup_code <> '15' -- Not Applicable.
2849: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2850: and hl1.lookup_code <> '15' -- Not Applicable.
2851: 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)
2852: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2853: 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)
2854: AND hl2.lookup_code <> '15' -- Not Applicable.
2855: group by paaf.business_group_id,

Line 2853: 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)

2849: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2850: and hl1.lookup_code <> '15' -- Not Applicable.
2851: 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)
2852: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2853: 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)
2854: AND hl2.lookup_code <> '15' -- Not Applicable.
2855: group by paaf.business_group_id,
2856: paei.aei_information7,
2857: haou.name,

Line 2859: 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)),

2855: group by paaf.business_group_id,
2856: paei.aei_information7,
2857: haou.name,
2858: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2859: 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)),
2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2861: hl.lookup_code,
2862: 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'),
2863: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),

2856: paei.aei_information7,
2857: haou.name,
2858: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2859: 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)),
2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2861: hl.lookup_code,
2862: 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'),
2863: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2864: decode(papf.PER_INFORMATION3,null,null,

Line 2862: 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'),

2858: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2859: 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)),
2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2861: hl.lookup_code,
2862: 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'),
2863: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2864: decode(papf.PER_INFORMATION3,null,null,
2865: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2866: -to_char(p_report_date,'YYYYMMDD'))

Line 2882: insert into per_za_employment_equity

2878:
2879: commit;
2880:
2881: -- Inserts non-associated occupational levels with zero values
2882: insert into per_za_employment_equity
2883: (
2884: report_id,
2885: reporting_date,
2886: business_group_id,

Line 2926: from per_za_employment_equity pzee

2922: , hr_all_organization_units haou
2923: where not exists
2924: (
2925: select 'X'
2926: from per_za_employment_equity pzee
2927: where pzee.level_cat_code = hl.lookup_code
2928: and pzee.report_id = 'EQ5'
2929: and pzee.business_group_id = p_business_group_id --Bug 4872110
2930: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2939: insert into per_za_employment_equity

2935: and haou.business_group_id = p_business_group_id --Bug 4872110
2936: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2937:
2938: -- Inserts non-associated occupational levels with zero values
2939: insert into per_za_employment_equity
2940: (
2941: report_id,
2942: reporting_date,
2943: business_group_id,

Line 2983: from per_za_employment_equity pzee

2979: , hr_all_organization_units haou
2980: where not exists
2981: (
2982: select 'X'
2983: from per_za_employment_equity pzee
2984: where pzee.level_cat_code = hl.lookup_code
2985: and pzee.report_id = 'EQ5F'
2986: and pzee.business_group_id = p_business_group_id --Bug 4872110
2987: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3003: insert into per_za_employment_equity

2999: -- the twelve months preceding this report)
3000: elsif p_report_code = 'EQ6' then
3001:
3002: -- Populate with Promotions
3003: insert into per_za_employment_equity
3004: (
3005: report_id,
3006: reporting_date,
3007: business_group_id,

Line 3034: 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

3030: paaf.business_group_id,
3031: paei.aei_information7 legal_entity_id,
3032: haou.name legal_entity,
3033: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3034: 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
3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
3036: hl.lookup_code lookup_code,
3037: 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,
3038: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,

3031: paei.aei_information7 legal_entity_id,
3032: haou.name legal_entity,
3033: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3034: 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
3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
3036: hl.lookup_code lookup_code,
3037: 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,
3038: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3039: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 3037: 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,

3033: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3034: 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
3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
3036: hl.lookup_code lookup_code,
3037: 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,
3038: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3039: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3040: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3041: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 3080: 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)

3076: and paei.aei_information7 is not null
3077: and nvl(paei.aei_information6, 'N') <> 'Y'
3078: and haou.organization_id = paei.aei_information7
3079: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3080: 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)
3081: AND hl.lookup_code <> '15' -- Not Applicable.
3082: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3083: and hl1.lookup_code <> '15' -- Not Applicable.
3084: 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)

Line 3084: 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)

3080: 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)
3081: AND hl.lookup_code <> '15' -- Not Applicable.
3082: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3083: and hl1.lookup_code <> '15' -- Not Applicable.
3084: 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)
3085: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3086: 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)
3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: 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') <

Line 3086: 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)

3082: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3083: and hl1.lookup_code <> '15' -- Not Applicable.
3084: 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)
3085: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3086: 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)
3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: 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') <
3089: any
3090: (

Line 3088: 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') <

3084: 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)
3085: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3086: 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)
3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: 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') <
3089: any
3090: (
3091: 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
3092: from per_all_assignments_f paaf1

Line 3091: 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

3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: 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') <
3089: any
3090: (
3091: 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
3092: from per_all_assignments_f paaf1
3093: where paaf1.person_id = papf.person_id
3094: and paaf1.primary_flag = 'Y'
3095: 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

Line 3095: 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

3091: 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
3092: from per_all_assignments_f paaf1
3093: where paaf1.person_id = papf.person_id
3094: and paaf1.primary_flag = 'Y'
3095: 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
3096: and paaf1.effective_end_date + 1 = paaf.effective_start_date
3097: )
3098: group by paaf.business_group_id,
3099: paei.aei_information7,

Line 3102: 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)),

3098: group by paaf.business_group_id,
3099: paei.aei_information7,
3100: haou.name,
3101: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3102: 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)),
3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3104: hl.lookup_code,
3105: 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'),
3106: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),

3099: paei.aei_information7,
3100: haou.name,
3101: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3102: 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)),
3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3104: hl.lookup_code,
3105: 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'),
3106: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3107: decode(papf.PER_INFORMATION3,null,null,

Line 3105: 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'),

3101: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3102: 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)),
3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3104: hl.lookup_code,
3105: 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'),
3106: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3107: decode(papf.PER_INFORMATION3,null,null,
3108: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3109: -to_char(p_report_date,'YYYYMMDD'))

Line 3115: insert into per_za_employment_equity

3111:
3112: commit;
3113:
3114: -- Inserts non-associated occupational levels with zero values
3115: insert into per_za_employment_equity
3116: (
3117: report_id,
3118: reporting_date,
3119: business_group_id,

Line 3159: from per_za_employment_equity pzee

3155: , hr_all_organization_units haou
3156: where not exists
3157: (
3158: select 'X'
3159: from per_za_employment_equity pzee
3160: where pzee.level_cat_code = hl.lookup_code
3161: and pzee.report_id = 'EQ6'
3162: and pzee.business_group_id = p_business_group_id --Bug 4872110
3163: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3172: insert into per_za_employment_equity

3168: and haou.business_group_id = p_business_group_id --Bug 4872110
3169: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3170:
3171: -- Inserts non-associated occupational levels with zero values
3172: insert into per_za_employment_equity
3173: (
3174: report_id,
3175: reporting_date,
3176: business_group_id,

Line 3216: from per_za_employment_equity pzee

3212: , hr_all_organization_units haou
3213: where not exists
3214: (
3215: select 'X'
3216: from per_za_employment_equity pzee
3217: where pzee.level_cat_code = hl.lookup_code
3218: and pzee.report_id = 'EQ6F'
3219: and pzee.business_group_id = p_business_group_id --Bug 4872110
3220: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3237: insert into per_za_employment_equity

3233: -- the twelve months preceding this report)
3234: elsif p_report_code = 'EQ7' then
3235:
3236: -- Populate with Terminations
3237: insert into per_za_employment_equity
3238: (
3239: report_id,
3240: reporting_date,
3241: business_group_id,

Line 3289: 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

3285: paaf.business_group_id,
3286: paei.aei_information7 legal_entity_id,
3287: haou.name legal_entity,
3288: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3289: 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
3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3291: hl.lookup_code meaning_code,
3292: 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,
3293: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

3286: paei.aei_information7 legal_entity_id,
3287: haou.name legal_entity,
3288: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3289: 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
3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3291: hl.lookup_code meaning_code,
3292: 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,
3293: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3294: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 3292: 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,

3288: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3289: 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
3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3291: hl.lookup_code meaning_code,
3292: 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,
3293: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3294: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3295: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3296: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 3332: 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)

3328: and paei.aei_information7 is not null
3329: and nvl(paei.aei_information6, 'N') <> 'Y'
3330: and haou.organization_id = paei.aei_information7
3331: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3332: 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)
3333: AND hl.lookup_code <> '15' -- Not Applicable.
3334: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3335: and hl1.lookup_code <> '15' -- Not Applicable
3336: 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)

Line 3336: 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)

3332: 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)
3333: AND hl.lookup_code <> '15' -- Not Applicable.
3334: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3335: and hl1.lookup_code <> '15' -- Not Applicable
3336: 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)
3337: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3338: 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)
3339: AND hl2.lookup_code <> '15' -- Not Applicable.
3340: group by paaf.business_group_id,

Line 3338: 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)

3334: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3335: and hl1.lookup_code <> '15' -- Not Applicable
3336: 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)
3337: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3338: 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)
3339: AND hl2.lookup_code <> '15' -- Not Applicable.
3340: group by paaf.business_group_id,
3341: paei.aei_information7,
3342: haou.name,

Line 3344: 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)),

3340: group by paaf.business_group_id,
3341: paei.aei_information7,
3342: haou.name,
3343: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3344: 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)),
3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3346: hl.lookup_code,
3347: 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'),
3348: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),

3341: paei.aei_information7,
3342: haou.name,
3343: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3344: 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)),
3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3346: hl.lookup_code,
3347: 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'),
3348: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3349: decode(papf.PER_INFORMATION3,null,null,

Line 3347: 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'),

3343: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3344: 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)),
3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3346: hl.lookup_code,
3347: 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'),
3348: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3349: decode(papf.PER_INFORMATION3,null,null,
3350: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3351: -to_char(p_report_date,'YYYYMMDD'))

Line 3368: insert into per_za_employment_equity

3364:
3365: commit;
3366:
3367: -- Inserts non-associated occupational levels with zero values
3368: insert into per_za_employment_equity
3369: (
3370: report_id,
3371: reporting_date,
3372: business_group_id,

Line 3412: from per_za_employment_equity pzee

3408: , hr_all_organization_units haou
3409: where not exists
3410: (
3411: select 'X'
3412: from per_za_employment_equity pzee
3413: where pzee.level_cat_code = hl.lookup_code
3414: and pzee.report_id = 'EQ7'
3415: and pzee.business_group_id = p_business_group_id --Bug 4872110
3416: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3424: insert into per_za_employment_equity

3420: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3421: and haou.business_group_id = p_business_group_id --Bug 4872110
3422: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3423:
3424: insert into per_za_employment_equity
3425: (
3426: report_id,
3427: reporting_date,
3428: business_group_id,

Line 3468: from per_za_employment_equity pzee

3464: , hr_all_organization_units haou
3465: where not exists
3466: (
3467: select 'X'
3468: from per_za_employment_equity pzee
3469: where pzee.level_cat_code = hl.lookup_code
3470: and pzee.report_id = 'EQ7F'
3471: and pzee.business_group_id = p_business_group_id --Bug 4872110
3472: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3488: insert into per_za_employment_equity

3484: -- category during the twelve months preceding this report)
3485: elsif p_report_code = 'EQ8' then
3486:
3487: -- Populate with Termination Reason totals
3488: insert into per_za_employment_equity
3489: (
3490: report_id,
3491: reporting_date,
3492: business_group_id,

Line 3555: 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

3551: paaf.business_group_id,
3552: paei.aei_information7 legal_entity_id,
3553: haou.name legal_entity,
3554: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3555: 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
3556: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3557: ppos.leaving_reason meaning_code,
3558: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 3556: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

3552: paei.aei_information7 legal_entity_id,
3553: haou.name legal_entity,
3554: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3555: 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
3556: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3557: ppos.leaving_reason meaning_code,
3558: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3560: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 3558: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,

3554: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3555: 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
3556: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3557: ppos.leaving_reason meaning_code,
3558: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3560: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3561: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3562: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 3597: 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'

3593: and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
3594: and paei.aei_information7 is not null
3595: and nvl(paei.aei_information6, 'N') <> 'Y'
3596: and haou.organization_id = paei.aei_information7
3597: 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'
3598: 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'
3599: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3600: 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)
3601: and hl.lookup_code <> '15' -- Not Applicable

Line 3598: 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'

3594: and paei.aei_information7 is not null
3595: and nvl(paei.aei_information6, 'N') <> 'Y'
3596: and haou.organization_id = paei.aei_information7
3597: 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'
3598: 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'
3599: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3600: 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)
3601: and hl.lookup_code <> '15' -- Not Applicable
3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'

Line 3600: 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)

3596: and haou.organization_id = paei.aei_information7
3597: 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'
3598: 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'
3599: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3600: 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)
3601: and hl.lookup_code <> '15' -- Not Applicable
3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3603: and hl1.lookup_code <> '15' -- Not Applicable
3604: 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)

Line 3604: 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)

3600: 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)
3601: and hl.lookup_code <> '15' -- Not Applicable
3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3603: and hl1.lookup_code <> '15' -- Not Applicable
3604: 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)
3605: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3606: 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)
3607: and hl2.lookup_code <> '15' -- Not Applicable
3608: group by paaf.business_group_id,

Line 3606: 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)

3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3603: and hl1.lookup_code <> '15' -- Not Applicable
3604: 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)
3605: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3606: 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)
3607: and hl2.lookup_code <> '15' -- Not Applicable
3608: group by paaf.business_group_id,
3609: paei.aei_information7,
3610: haou.name,

Line 3612: 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)),

3608: group by paaf.business_group_id,
3609: paei.aei_information7,
3610: haou.name,
3611: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3612: 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)),
3613: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3614: ppos.leaving_reason,
3615: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3616: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 3613: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),

3609: paei.aei_information7,
3610: haou.name,
3611: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3612: 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)),
3613: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3614: ppos.leaving_reason,
3615: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3616: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3617: decode(papf.PER_INFORMATION3,null,null,

Line 3615: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),

3611: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3612: 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)),
3613: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3614: ppos.leaving_reason,
3615: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3616: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3617: decode(papf.PER_INFORMATION3,null,null,
3618: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3619: -to_char(p_report_date,'YYYYMMDD'))

Line 3653: insert into per_za_employment_equity

3649: else
3650: l_reason := 'Other';
3651: end if;
3652:
3653: insert into per_za_employment_equity
3654: (
3655: report_id,
3656: reporting_date,
3657: business_group_id,

Line 3711: from per_za_employment_equity pzee

3707: from hr_all_organization_units haou
3708: where not exists
3709: (
3710: select 'X'
3711: from per_za_employment_equity pzee
3712: where pzee.level_cat = l_reason
3713: and pzee.report_id = 'EQ8'
3714: and pzee.business_group_id = p_business_group_id --Bug 4872110
3715: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3723: insert into per_za_employment_equity

3719: and haou.business_group_id = p_business_group_id --Bug 4872110
3720: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3721:
3722:
3723: insert into per_za_employment_equity
3724: (
3725: report_id,
3726: reporting_date,
3727: business_group_id,

Line 3781: from per_za_employment_equity pzee

3777: from hr_all_organization_units haou
3778: where not exists
3779: (
3780: select 'X'
3781: from per_za_employment_equity pzee
3782: where pzee.level_cat = l_reason
3783: and pzee.report_id = 'EQ8F'
3784: and pzee.business_group_id = p_business_group_id --Bug 4872110
3785: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3954: DELETE FROM per_za_employment_equity

3950: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
3951: ) is
3952:
3953: begin
3954: DELETE FROM per_za_employment_equity
3955: Where REPORT_ID IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8',
3956: 'EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F'
3957: );
3958:

Line 4016: insert into per_za_employment_equity

4012: , p_legal_entity_id =>p_legal_entity_id
4013: );
4014:
4015: -- Inserts non-associated occupational categories with zero values
4016: insert into per_za_employment_equity
4017: (
4018: report_id,
4019: reporting_date,
4020: business_group_id,

Line 4055: from per_za_employment_equity pzee1

4051: 0,
4052: 0,
4053: 0,
4054: 0
4055: from per_za_employment_equity pzee1
4056: Where pzee1.business_group_id = p_business_group_id
4057: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
4058: AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
4059: AND not exists

Line 4062: from per_za_employment_equity pzee

4058: AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
4059: AND not exists
4060: (
4061: select 'X'
4062: from per_za_employment_equity pzee
4063: where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
4064: AND pzee.legal_entity_id = pzee1.legal_entity_id
4065: AND pzee.report_id ||'F' = pzee1.report_id
4066: AND pzee1.level_cat_code = pzee.level_cat_code

Line 4074: insert into per_za_employment_equity

4070: );
4071:
4072:
4073: -- Inserts non-associated occupational categories with zero values
4074: insert into per_za_employment_equity
4075: (
4076: report_id,
4077: reporting_date,
4078: business_group_id,

Line 4113: from per_za_employment_equity pzee1

4109: 0,
4110: 0,
4111: 0,
4112: 0
4113: from per_za_employment_equity pzee1
4114: Where pzee1.business_group_id = p_business_group_id
4115: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
4116: AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
4117: AND not exists

Line 4120: from per_za_employment_equity pzee

4116: AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
4117: AND not exists
4118: (
4119: select 'X'
4120: from per_za_employment_equity pzee
4121: where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
4122: AND pzee.legal_entity_id = pzee1.legal_entity_id
4123: AND pzee1.report_id ||'F' = pzee.report_id
4124: AND pzee1.level_cat_code = pzee.level_cat_code

Line 4586: 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,

4582: paaf.payroll_id,
4583: paei.aei_information7 ,
4584: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4585: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4586: 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,
4587: 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,
4588: paaf.pay_basis_id
4589: from per_assignment_extra_info paei,
4590: per_assignment_status_types past,

Line 4587: 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,

4583: paei.aei_information7 ,
4584: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4585: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4586: 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,
4587: 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,
4588: paaf.pay_basis_id
4589: from per_assignment_extra_info paei,
4590: per_assignment_status_types past,
4591: per_all_assignments_f paaf,

Line 4612: 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)

4608: AND hl_fn.lookup_code <>'15'
4609: AND hl_cat.application_id = '800'
4610: AND hl_lev.application_id = '800'
4611: AND hl_fn.application_id = '800'
4612: 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)
4613: 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)
4614: 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)
4615: and nvl(paei.aei_information6, 'N') <> 'Y'
4616: order BY paei.aei_information7, paaf.payroll_id;

Line 4613: 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)

4609: AND hl_cat.application_id = '800'
4610: AND hl_lev.application_id = '800'
4611: AND hl_fn.application_id = '800'
4612: 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)
4613: 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)
4614: 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)
4615: and nvl(paei.aei_information6, 'N') <> 'Y'
4616: order BY paei.aei_information7, paaf.payroll_id;
4617: */

Line 4614: 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)

4610: AND hl_lev.application_id = '800'
4611: AND hl_fn.application_id = '800'
4612: 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)
4613: 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)
4614: 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)
4615: and nvl(paei.aei_information6, 'N') <> 'Y'
4616: order BY paei.aei_information7, paaf.payroll_id;
4617: */
4618:

Line 4626: 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,

4622: paaf.payroll_id,
4623: paei.aei_information7 ,
4624: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4625: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4626: 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,
4627: 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,
4628: paaf.pay_basis_id
4629: from per_assignment_extra_info paei,
4630: per_all_assignments_f paaf,

Line 4627: 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,

4623: paei.aei_information7 ,
4624: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4625: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4626: 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,
4627: 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,
4628: paaf.pay_basis_id
4629: from per_assignment_extra_info paei,
4630: per_all_assignments_f paaf,
4631: hr_lookups hl_cat,

Line 4657: 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)

4653: AND hl_fn.lookup_code <>'15'
4654: AND hl_cat.application_id = '800'
4655: AND hl_lev.application_id = '800'
4656: AND hl_fn.application_id = '800'
4657: 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)
4658: 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)
4659: 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)
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;

Line 4658: 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)

4654: AND hl_cat.application_id = '800'
4655: AND hl_lev.application_id = '800'
4656: AND hl_fn.application_id = '800'
4657: 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)
4658: 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)
4659: 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)
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;
4662:

Line 4659: 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)

4655: AND hl_lev.application_id = '800'
4656: AND hl_fn.application_id = '800'
4657: 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)
4658: 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)
4659: 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)
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;
4662:
4663:

Line 5137: INSERT INTO per_za_employment_equity

5133:
5134: l_rowind := g_cat_Enc_Diff_table.first;
5135: loop
5136: exit when l_rowind is null;
5137: INSERT INTO per_za_employment_equity
5138: (
5139: report_id,
5140: reporting_date,
5141: business_group_id,

Line 5180: INSERT INTO per_za_employment_equity

5176: FROM hr_all_organization_units haou
5177: Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
5178:
5179:
5180: INSERT INTO per_za_employment_equity
5181: (
5182: report_id,
5183: reporting_date,
5184: business_group_id,

Line 5228: INSERT INTO per_za_employment_equity

5224:
5225: l_rowind := g_lev_Enc_Diff_table.first;
5226: loop
5227: exit when l_rowind is null;
5228: INSERT INTO per_za_employment_equity
5229: (
5230: report_id,
5231: reporting_date,
5232: business_group_id,

Line 5270: INSERT INTO per_za_employment_equity

5266: , g_lev_Enc_Diff_table(l_rowind).total
5267: FROM hr_all_organization_units haou
5268: Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
5269:
5270: INSERT INTO per_za_employment_equity
5271: (
5272: report_id,
5273: reporting_date,
5274: business_group_id,

Line 5317: insert into per_za_employment_equity

5313: END loop;
5314: --hr_utility.trace_off;
5315:
5316: -- Inserts non-associated occupational categories with zero values for no of employees
5317: insert into per_za_employment_equity
5318: (
5319: report_id,
5320: reporting_date,
5321: business_group_id,

Line 5361: from per_za_employment_equity pzee

5357: , hr_all_organization_units haou
5358: where not exists
5359: (
5360: select 'X'
5361: from per_za_employment_equity pzee
5362: where pzee.level_cat_code = hl.lookup_code
5363: and pzee.report_id = 'ED1'
5364: and pzee.business_group_id = p_business_group_id --Bug 4872110
5365: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5372: insert into per_za_employment_equity

5368: and haou.business_group_id = p_business_group_id --Bug 4872110
5369: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5370:
5371: -- Inserts non-associated occupational categories with zero values for no of employees
5372: insert into per_za_employment_equity
5373: (
5374: report_id,
5375: reporting_date,
5376: business_group_id,

Line 5416: from per_za_employment_equity pzee

5412: , hr_all_organization_units haou
5413: where not exists
5414: (
5415: select 'X'
5416: from per_za_employment_equity pzee
5417: where pzee.level_cat_code = hl.lookup_code
5418: and pzee.report_id = 'ED1I'
5419: and pzee.business_group_id = p_business_group_id --Bug 4872110
5420: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5427: insert into per_za_employment_equity

5423: and haou.business_group_id = p_business_group_id --Bug 4872110
5424: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5425:
5426: -- inserting 0 values for the no of employees
5427: insert into per_za_employment_equity
5428: (
5429: report_id,
5430: reporting_date,
5431: business_group_id,

Line 5471: from per_za_employment_equity pzee

5467: , hr_all_organization_units haou
5468: where not exists
5469: (
5470: select 'X'
5471: from per_za_employment_equity pzee
5472: where pzee.level_cat_code = hl.lookup_code
5473: and pzee.report_id = 'ED2'
5474: and pzee.business_group_id = p_business_group_id --Bug 4872110
5475: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5482: insert into per_za_employment_equity

5478: and haou.business_group_id = p_business_group_id --Bug 4872110
5479: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5480:
5481: -- inserting 0 values for the Income
5482: insert into per_za_employment_equity
5483: (
5484: report_id,
5485: reporting_date,
5486: business_group_id,

Line 5526: from per_za_employment_equity pzee

5522: , hr_all_organization_units haou
5523: where not exists
5524: (
5525: select 'X'
5526: from per_za_employment_equity pzee
5527: where pzee.level_cat_code = hl.lookup_code
5528: and pzee.report_id = 'ED2I'
5529: and pzee.business_group_id = p_business_group_id --Bug 4872110
5530: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5560: DELETE FROM per_za_employment_equity

5556: g_lev_Enc_Diff_table_F.delete;
5557: g_lev_Enc_Diff_table_T.delete;
5558: g_lev_Enc_Diff_table_TF.delete;
5559:
5560: DELETE FROM per_za_employment_equity
5561: Where report_id IN
5562: ( 'ED', 'EDI', 'EDF','EDFI');
5563:
5564: end reset_new_tables;

Line 5983: 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,

5979: paaf.payroll_id,
5980: paei.aei_information7 ,
5981: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
5982: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
5983: 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,
5984: 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,
5985: paaf.pay_basis_id
5986: from per_assignment_extra_info paei,
5987: per_assignment_status_types past,

Line 5984: 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,

5980: paei.aei_information7 ,
5981: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
5982: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
5983: 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,
5984: 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,
5985: paaf.pay_basis_id
5986: from per_assignment_extra_info paei,
5987: per_assignment_status_types past,
5988: per_all_assignments_f paaf,

Line 6009: 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)

6005: AND hl_fn.lookup_code <>'15'
6006: AND hl_cat.application_id = '800'
6007: AND hl_lev.application_id = '800'
6008: AND hl_fn.application_id = '800'
6009: 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)
6010: 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)
6011: 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)
6012: and nvl(paei.aei_information6, 'N') <> 'Y'
6013: order BY paei.aei_information7, paaf.payroll_id;

Line 6010: 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)

6006: AND hl_cat.application_id = '800'
6007: AND hl_lev.application_id = '800'
6008: AND hl_fn.application_id = '800'
6009: 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)
6010: 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)
6011: 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)
6012: and nvl(paei.aei_information6, 'N') <> 'Y'
6013: order BY paei.aei_information7, paaf.payroll_id;
6014: */

Line 6011: 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)

6007: AND hl_lev.application_id = '800'
6008: AND hl_fn.application_id = '800'
6009: 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)
6010: 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)
6011: 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)
6012: and nvl(paei.aei_information6, 'N') <> 'Y'
6013: order BY paei.aei_information7, paaf.payroll_id;
6014: */
6015:

Line 6023: 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,

6019: paaf.person_id, -- Bug 4413678
6020: paaf.payroll_id,
6021: paei.aei_information7 ,
6022: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
6023: 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,
6024: 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,
6025: paaf.pay_basis_id
6026: from per_assignment_extra_info paei,
6027: per_all_assignments_f paaf,

Line 6024: 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,

6020: paaf.payroll_id,
6021: paei.aei_information7 ,
6022: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
6023: 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,
6024: 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,
6025: paaf.pay_basis_id
6026: from per_assignment_extra_info paei,
6027: per_all_assignments_f paaf,
6028: hr_lookups hl_lev,

Line 6050: 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)

6046: AND hl_lev.lookup_code <> '15'
6047: AND hl_fn.lookup_code <>'15'
6048: AND hl_lev.application_id = '800'
6049: AND hl_fn.application_id = '800'
6050: 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)
6051: 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)
6052: and nvl(paei.aei_information6, 'N') <> 'Y'
6053: order BY paei.aei_information7, paaf.payroll_id;
6054:

Line 6051: 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)

6047: AND hl_fn.lookup_code <>'15'
6048: AND hl_lev.application_id = '800'
6049: AND hl_fn.application_id = '800'
6050: 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)
6051: 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)
6052: and nvl(paei.aei_information6, 'N') <> 'Y'
6053: order BY paei.aei_information7, paaf.payroll_id;
6054:
6055:

Line 6604: INSERT INTO per_za_employment_equity

6600: l_rowind := g_lev_Enc_Diff_table.first;
6601: loop
6602: exit when l_rowind is null;
6603:
6604: INSERT INTO per_za_employment_equity
6605: (
6606: report_id,
6607: reporting_date,
6608: business_group_id,

Line 6646: INSERT INTO per_za_employment_equity

6642: , g_lev_Enc_Diff_table(l_rowind).total
6643: FROM hr_all_organization_units haou
6644: Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
6645:
6646: INSERT INTO per_za_employment_equity
6647: (
6648: report_id,
6649: reporting_date,
6650: business_group_id,

Line 6695: INSERT INTO per_za_employment_equity

6691: --Permanent Foreigners
6692: l_rowind := g_lev_Enc_Diff_table_F.first;
6693: loop
6694: exit when l_rowind is null;
6695: INSERT INTO per_za_employment_equity
6696: (
6697: report_id,
6698: reporting_date,
6699: business_group_id,

Line 6737: INSERT INTO per_za_employment_equity

6733: , g_lev_Enc_Diff_table_F(l_rowind).total
6734: FROM hr_all_organization_units haou
6735: Where haou.organization_id = g_lev_Enc_Diff_table_F(l_rowind).legal_entity_id;
6736:
6737: INSERT INTO per_za_employment_equity
6738: (
6739: report_id,
6740: reporting_date,
6741: business_group_id,

Line 6787: INSERT INTO per_za_employment_equity

6783: l_rowind := g_lev_Enc_Diff_table_T.first;
6784: loop
6785: exit when l_rowind is null;
6786: --Non permanent workers
6787: INSERT INTO per_za_employment_equity
6788: (
6789: report_id,
6790: reporting_date,
6791: business_group_id,

Line 6829: INSERT INTO per_za_employment_equity

6825: , g_lev_Enc_Diff_table_T(l_rowind).total
6826: FROM hr_all_organization_units haou
6827: Where haou.organization_id = g_lev_Enc_Diff_table_T(l_rowind).legal_entity_id;
6828:
6829: INSERT INTO per_za_employment_equity
6830: (
6831: report_id,
6832: reporting_date,
6833: business_group_id,

Line 6879: INSERT INTO per_za_employment_equity

6875: --Non permanent foreigners workers
6876: l_rowind := g_lev_Enc_Diff_table_TF.first;
6877: loop
6878: exit when l_rowind is null;
6879: INSERT INTO per_za_employment_equity
6880: (
6881: report_id,
6882: reporting_date,
6883: business_group_id,

Line 6921: INSERT INTO per_za_employment_equity

6917: , g_lev_Enc_Diff_table_TF(l_rowind).total
6918: FROM hr_all_organization_units haou
6919: Where haou.organization_id = g_lev_Enc_Diff_table_TF(l_rowind).legal_entity_id;
6920:
6921: INSERT INTO per_za_employment_equity
6922: (
6923: report_id,
6924: reporting_date,
6925: business_group_id,

Line 6969: insert into per_za_employment_equity

6965: --hr_utility.trace_off;
6966:
6967:
6968: -- inserting 0 values for the no of employees
6969: insert into per_za_employment_equity
6970: (
6971: report_id,
6972: reporting_date,
6973: business_group_id,

Line 7013: from per_za_employment_equity pzee

7009: , hr_all_organization_units haou
7010: where not exists
7011: (
7012: select 'X'
7013: from per_za_employment_equity pzee
7014: where pzee.level_cat_code = hl.lookup_code
7015: and pzee.report_id = 'ED'
7016: and pzee.business_group_id = p_business_group_id --Bug 4872110
7017: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7025: insert into per_za_employment_equity

7021: and haou.business_group_id = p_business_group_id --Bug 4872110
7022: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
7023:
7024: -- inserting 0 values for the Income
7025: insert into per_za_employment_equity
7026: (
7027: report_id,
7028: reporting_date,
7029: business_group_id,

Line 7069: from per_za_employment_equity pzee

7065: , hr_all_organization_units haou
7066: where not exists
7067: (
7068: select 'X'
7069: from per_za_employment_equity pzee
7070: where pzee.level_cat_code = hl.lookup_code
7071: and pzee.report_id = 'EDI'
7072: and pzee.business_group_id = p_business_group_id --Bug 4872110
7073: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7081: insert into per_za_employment_equity

7077: and haou.business_group_id = p_business_group_id --Bug 4872110
7078: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
7079:
7080: -- inserting 0 values for the no of employees
7081: insert into per_za_employment_equity
7082: (
7083: report_id,
7084: reporting_date,
7085: business_group_id,

Line 7125: from per_za_employment_equity pzee

7121: , hr_all_organization_units haou
7122: where not exists
7123: (
7124: select 'X'
7125: from per_za_employment_equity pzee
7126: where pzee.level_cat_code = hl.lookup_code
7127: and pzee.report_id = 'EDF'
7128: and pzee.business_group_id = p_business_group_id --Bug 4872110
7129: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7137: insert into per_za_employment_equity

7133: and haou.business_group_id = p_business_group_id --Bug 4872110
7134: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
7135:
7136: -- inserting 0 values for the Income
7137: insert into per_za_employment_equity
7138: (
7139: report_id,
7140: reporting_date,
7141: business_group_id,

Line 7181: from per_za_employment_equity pzee

7177: , hr_all_organization_units haou
7178: where not exists
7179: (
7180: select 'X'
7181: from per_za_employment_equity pzee
7182: where pzee.level_cat_code = hl.lookup_code
7183: and pzee.report_id = 'EDFI'
7184: and pzee.business_group_id = p_business_group_id --Bug 4872110
7185: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7195: insert into per_za_employment_equity

7191:
7192: --Insert rows for non permanent
7193: --Rows present for Non foreigner non permanent, but not present for
7194: --foreigner non permanent
7195: insert into per_za_employment_equity
7196: (
7197: report_id,
7198: reporting_date,
7199: business_group_id,

Line 7234: from per_za_employment_equity pzee1

7230: 0,
7231: 0,
7232: 0,
7233: 0
7234: from per_za_employment_equity pzee1
7235: Where pzee1.business_group_id = p_business_group_id
7236: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
7237: AND pzee1.report_id IN ('ED','EDI')
7238: AND not exists

Line 7241: from per_za_employment_equity pzee

7237: AND pzee1.report_id IN ('ED','EDI')
7238: AND not exists
7239: (
7240: select 'X'
7241: from per_za_employment_equity pzee
7242: where pzee.business_group_id = pzee1.business_group_id
7243: AND pzee.legal_entity_id = pzee1.legal_entity_id
7244: AND ( pzee1.report_id ||'F' = pzee.report_id --row not present for ED
7245: OR

Line 7255: insert into per_za_employment_equity

7251:
7252: --Insert rows for non permanent
7253: --Rows present for foreigner non permanent, but not present for
7254: --non foreigner non permanent
7255: insert into per_za_employment_equity
7256: (
7257: report_id,
7258: reporting_date,
7259: business_group_id,

Line 7294: from per_za_employment_equity pzee1

7290: 0,
7291: 0,
7292: 0,
7293: 0
7294: from per_za_employment_equity pzee1
7295: Where pzee1.business_group_id = p_business_group_id
7296: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
7297: AND pzee1.report_id IN ('EDF','EDFI')
7298: AND not exists

Line 7301: from per_za_employment_equity pzee

7297: AND pzee1.report_id IN ('EDF','EDFI')
7298: AND not exists
7299: (
7300: select 'X'
7301: from per_za_employment_equity pzee
7302: where pzee.business_group_id = pzee1.business_group_id
7303: AND pzee.legal_entity_id = pzee1.legal_entity_id
7304: AND ( pzee.report_id ||'F' = pzee1.report_id --row not present for ED
7305: OR

Line 7406: insert into per_za_employment_equity

7402: if p_report_code = 'EQ1' then
7403:
7404: -- Note: The date effective select on per_all_assignments_f is ok in this case, since an assignment
7405: -- record always exist at the same time as an employee record with status EMP
7406: insert into per_za_employment_equity
7407: (
7408: report_id,
7409: reporting_date,
7410: business_group_id,

Line 7438: 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

7434: paaf.business_group_id,
7435: paei.aei_information7 legal_entity_id,
7436: haou.name legal_entity,
7437: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7438: 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
7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7440: hl.lookup_code meaning_code,
7441: 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,
7442: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

7435: paei.aei_information7 legal_entity_id,
7436: haou.name legal_entity,
7437: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7438: 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
7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7440: hl.lookup_code meaning_code,
7441: 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,
7442: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7443: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 7441: 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,

7437: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7438: 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
7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7440: hl.lookup_code meaning_code,
7441: 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,
7442: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7443: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
7444: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
7445: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 7478: 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)

7474: and paei.aei_information7 is not null
7475: and nvl(paei.aei_information6, 'N') <> 'Y'
7476: and haou.organization_id = paei.aei_information7
7477: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7478: 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)
7479: AND hl.lookup_code <> '15' -- Not Applicable.
7480: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7481: AND hl1.lookup_code <> '15' -- Not Applicable.
7482: 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)

Line 7482: 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)

7478: 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)
7479: AND hl.lookup_code <> '15' -- Not Applicable.
7480: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7481: AND hl1.lookup_code <> '15' -- Not Applicable.
7482: 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)
7483: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7484: AND hl2.lookup_code <> '15' -- Not Applicable.
7485: 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)
7486: group by paaf.business_group_id,

Line 7485: 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)

7481: AND hl1.lookup_code <> '15' -- Not Applicable.
7482: 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)
7483: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7484: AND hl2.lookup_code <> '15' -- Not Applicable.
7485: 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)
7486: group by paaf.business_group_id,
7487: paei.aei_information7,
7488: haou.name,
7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073

Line 7490: 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

7486: group by paaf.business_group_id,
7487: paei.aei_information7,
7488: haou.name,
7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7490: 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
7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7492: hl.lookup_code,
7493: 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'),
7494: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073

7487: paei.aei_information7,
7488: haou.name,
7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7490: 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
7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7492: hl.lookup_code,
7493: 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'),
7494: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7495: decode(papf.PER_INFORMATION11,null,null,

Line 7493: 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'),

7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7490: 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
7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7492: hl.lookup_code,
7493: 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'),
7494: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7495: decode(papf.PER_INFORMATION11,null,null,
7496: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
7497: -to_char(l_nat_date,'YYYYMMDD'))

Line 7504: insert into per_za_employment_equity

7500:
7501: commit;
7502:
7503: -- Inserts non-associated occupational categories with zero values
7504: insert into per_za_employment_equity
7505: (
7506: report_id,
7507: reporting_date,
7508: business_group_id,

Line 7548: from per_za_employment_equity pzee

7544: , hr_all_organization_units haou
7545: where not exists
7546: (
7547: select 'X'
7548: from per_za_employment_equity pzee
7549: where pzee.level_cat_code = hl.lookup_code
7550: and pzee.report_id = 'EQ1'
7551: and pzee.business_group_id = p_business_group_id --Bug 4872110
7552: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7561: insert into per_za_employment_equity

7557: and haou.business_group_id = p_business_group_id --Bug 4872110
7558: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
7559:
7560: -- Inseting 0 VALUES FOR FOREIGN nationals
7561: insert into per_za_employment_equity
7562: (
7563: report_id,
7564: reporting_date,
7565: business_group_id,

Line 7605: from per_za_employment_equity pzee

7601: , hr_all_organization_units haou
7602: where not exists
7603: (
7604: select 'X'
7605: from per_za_employment_equity pzee
7606: where pzee.level_cat_code = hl.lookup_code
7607: and pzee.report_id = 'EQ1F'
7608: and pzee.business_group_id = p_business_group_id --Bug 4872110
7609: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7625: insert into per_za_employment_equity

7621: -- 5. Occupational Levels (only employees with disabilities)
7622: elsif p_report_code = 'EQ2' then
7623:
7624: -- Populate with Occupational Level Totals
7625: insert into per_za_employment_equity
7626: (
7627: report_id,
7628: reporting_date,
7629: business_group_id,

Line 7657: 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

7653: paaf.business_group_id,
7654: paei.aei_information7 legal_entity_id,
7655: haou.name legal_entity,
7656: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7657: 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
7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7659: hl.lookup_code meaning_code,
7660: 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,
7661: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

7654: paei.aei_information7 legal_entity_id,
7655: haou.name legal_entity,
7656: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7657: 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
7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7659: hl.lookup_code meaning_code,
7660: 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,
7661: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7662: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 7660: 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,

7656: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7657: 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
7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7659: hl.lookup_code meaning_code,
7660: 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,
7661: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7662: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
7663: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
7664: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 7697: 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)

7693: and paei.aei_information7 is not null
7694: and nvl(paei.aei_information6, 'N') <> 'Y'
7695: and haou.organization_id = paei.aei_information7
7696: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7697: 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)
7698: AND hl.lookup_code <> '15' -- Not Applicable.
7699: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7700: AND hl1.lookup_code <> '15' -- Operation / core function
7701: 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)

Line 7701: 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)

7697: 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)
7698: AND hl.lookup_code <> '15' -- Not Applicable.
7699: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7700: AND hl1.lookup_code <> '15' -- Operation / core function
7701: 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)
7702: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7703: -- 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)
7704: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7705: group by paaf.business_group_id,

Line 7703: -- 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)

7699: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7700: AND hl1.lookup_code <> '15' -- Operation / core function
7701: 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)
7702: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7703: -- 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)
7704: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7705: group by paaf.business_group_id,
7706: paei.aei_information7,
7707: haou.name,

Line 7709: 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

7705: group by paaf.business_group_id,
7706: paei.aei_information7,
7707: haou.name,
7708: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7709: 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
7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7711: hl.lookup_code,
7712: 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'),
7713: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073

7706: paei.aei_information7,
7707: haou.name,
7708: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7709: 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
7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7711: hl.lookup_code,
7712: 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'),
7713: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7714: decode(papf.PER_INFORMATION11,null,null,

Line 7712: 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'),

7708: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7709: 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
7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7711: hl.lookup_code,
7712: 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'),
7713: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7714: decode(papf.PER_INFORMATION11,null,null,
7715: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
7716: -to_char(l_nat_date,'YYYYMMDD'))

Line 7722: insert into per_za_employment_equity

7718:
7719: commit;
7720:
7721: -- Inserts non-associated occupational levels with zero values
7722: insert into per_za_employment_equity
7723: (
7724: report_id,
7725: reporting_date,
7726: business_group_id,

Line 7766: from per_za_employment_equity pzee

7762: , hr_all_organization_units haou
7763: where not exists
7764: (
7765: select 'X'
7766: from per_za_employment_equity pzee
7767: where pzee.level_cat_code = hl.lookup_code
7768: and pzee.report_id = 'EQ2'
7769: and pzee.business_group_id = p_business_group_id --Bug 4872110
7770: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7780: insert into per_za_employment_equity

7776: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
7777:
7778:
7779: -- inserting 0 values for the Foreign Nationals
7780: insert into per_za_employment_equity
7781: (
7782: report_id,
7783: reporting_date,
7784: business_group_id,

Line 7824: from per_za_employment_equity pzee

7820: , hr_all_organization_units haou
7821: where not exists
7822: (
7823: select 'X'
7824: from per_za_employment_equity pzee
7825: where pzee.level_cat_code = hl.lookup_code
7826: and pzee.report_id = 'EQ2F'
7827: and pzee.business_group_id = p_business_group_id --Bug 4872110
7828: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 7843: insert into per_za_employment_equity

7839: -- For employment equity enhancement
7840: elsif p_report_code = 'EQ3' then
7841:
7842: -- Populate with New Hires
7843: insert into per_za_employment_equity
7844: (
7845: report_id,
7846: reporting_date,
7847: business_group_id,

Line 7894: 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

7890: paaf.business_group_id,
7891: paei.aei_information7 legal_entity_id,
7892: haou.name legal_entity,
7893: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
7894: 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
7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
7896: hl.lookup_code meaning_code,
7897: 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,
7898: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073

7891: paei.aei_information7 legal_entity_id,
7892: haou.name legal_entity,
7893: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
7894: 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
7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
7896: hl.lookup_code meaning_code,
7897: 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,
7898: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7899: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 7897: 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,

7893: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
7894: 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
7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
7896: hl.lookup_code meaning_code,
7897: 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,
7898: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7899: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
7900: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
7901: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 7934: 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)

7930: and paei.aei_information7 is not null
7931: and nvl(paei.aei_information6, 'N') <> 'Y'
7932: and haou.organization_id = paei.aei_information7
7933: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7934: 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)
7935: AND hl.lookup_code <> '15' -- Not Applicable.
7936: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7937: AND hl1.lookup_code = '1' -- Operation / core function
7938: 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)

Line 7938: 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)

7934: 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)
7935: AND hl.lookup_code <> '15' -- Not Applicable.
7936: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7937: AND hl1.lookup_code = '1' -- Operation / core function
7938: 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)
7939: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7940: -- 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)
7941: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7942: group by paaf.business_group_id,

Line 7940: -- 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)

7936: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7937: AND hl1.lookup_code = '1' -- Operation / core function
7938: 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)
7939: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7940: -- 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)
7941: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7942: group by paaf.business_group_id,
7943: paei.aei_information7,
7944: haou.name,

Line 7946: 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)),

7942: group by paaf.business_group_id,
7943: paei.aei_information7,
7944: haou.name,
7945: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7946: 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)),
7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
7948: hl.lookup_code,
7949: 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'),
7950: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),

7943: paei.aei_information7,
7944: haou.name,
7945: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7946: 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)),
7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
7948: hl.lookup_code,
7949: 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'),
7950: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7951: decode(papf.PER_INFORMATION11,null,null,

Line 7949: 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'),

7945: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7946: 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)),
7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
7948: hl.lookup_code,
7949: 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'),
7950: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7951: decode(papf.PER_INFORMATION11,null,null,
7952: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
7953: -to_char(l_nat_date,'YYYYMMDD'))

Line 7969: insert into per_za_employment_equity

7965:
7966: commit;
7967:
7968: -- Inserts non-associated occupational levels with zero values
7969: insert into per_za_employment_equity
7970: (
7971: report_id,
7972: reporting_date,
7973: business_group_id,

Line 8013: from per_za_employment_equity pzee

8009: , hr_all_organization_units haou
8010: where not exists
8011: (
8012: select 'X'
8013: from per_za_employment_equity pzee
8014: where pzee.level_cat_code = hl.lookup_code
8015: and pzee.report_id = 'EQ3'
8016: and pzee.business_group_id = p_business_group_id --Bug 4872110
8017: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8026: insert into per_za_employment_equity

8022: and haou.business_group_id = p_business_group_id --Bug 4872110
8023: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
8024:
8025: -- Inserts non-associated occupational levels with zero values
8026: insert into per_za_employment_equity
8027: (
8028: report_id,
8029: reporting_date,
8030: business_group_id,

Line 8070: from per_za_employment_equity pzee

8066: , hr_all_organization_units haou
8067: where not exists
8068: (
8069: select 'X'
8070: from per_za_employment_equity pzee
8071: where pzee.level_cat_code = hl.lookup_code
8072: and pzee.report_id = 'EQ3F'
8073: and pzee.business_group_id = p_business_group_id --Bug 4872110
8074: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8090: insert into per_za_employment_equity

8086: -- the twelve months preceding this report)
8087: elsif p_report_code = 'EQ4' then
8088:
8089: -- Populate with New Hires
8090: insert into per_za_employment_equity
8091: (
8092: report_id,
8093: reporting_date,
8094: business_group_id,

Line 8141: 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

8137: paaf.business_group_id,
8138: paei.aei_information7 legal_entity_id,
8139: haou.name legal_entity,
8140: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8141: 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
8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8143: hl.lookup_code meaning_code,
8144: 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,
8145: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073

8138: paei.aei_information7 legal_entity_id,
8139: haou.name legal_entity,
8140: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8141: 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
8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8143: hl.lookup_code meaning_code,
8144: 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,
8145: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8146: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 8144: 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,

8140: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8141: 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
8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8143: hl.lookup_code meaning_code,
8144: 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,
8145: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8146: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8147: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8148: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 8181: 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)

8177: and paei.aei_information7 is not null
8178: and nvl(paei.aei_information6, 'N') <> 'Y'
8179: and haou.organization_id = paei.aei_information7
8180: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8181: 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)
8182: AND hl.lookup_code <> '15' -- Not Applicable.
8183: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8184: AND hl1.lookup_code = '2' -- Support function
8185: 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)

Line 8185: 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)

8181: 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)
8182: AND hl.lookup_code <> '15' -- Not Applicable.
8183: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8184: AND hl1.lookup_code = '2' -- Support function
8185: 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)
8186: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8187: -- 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)
8188: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8189: group by paaf.business_group_id,

Line 8187: -- 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)

8183: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8184: AND hl1.lookup_code = '2' -- Support function
8185: 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)
8186: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8187: -- 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)
8188: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8189: group by paaf.business_group_id,
8190: paei.aei_information7,
8191: haou.name,

Line 8193: 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)),

8189: group by paaf.business_group_id,
8190: paei.aei_information7,
8191: haou.name,
8192: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8193: 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)),
8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8195: hl.lookup_code,
8196: 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'),
8197: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),

8190: paei.aei_information7,
8191: haou.name,
8192: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8193: 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)),
8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8195: hl.lookup_code,
8196: 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'),
8197: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8198: decode(papf.PER_INFORMATION11,null,null,

Line 8196: 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'),

8192: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8193: 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)),
8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8195: hl.lookup_code,
8196: 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'),
8197: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8198: decode(papf.PER_INFORMATION11,null,null,
8199: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8200: -to_char(l_nat_date,'YYYYMMDD'))

Line 8216: insert into per_za_employment_equity

8212:
8213: commit;
8214:
8215: -- Inserts non-associated occupational levels with zero values
8216: insert into per_za_employment_equity
8217: (
8218: report_id,
8219: reporting_date,
8220: business_group_id,

Line 8260: from per_za_employment_equity pzee

8256: , hr_all_organization_units haou
8257: where not exists
8258: (
8259: select 'X'
8260: from per_za_employment_equity pzee
8261: where pzee.level_cat_code = hl.lookup_code
8262: and pzee.report_id = 'EQ4'
8263: and pzee.business_group_id = p_business_group_id --Bug 4872110
8264: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8273: insert into per_za_employment_equity

8269: and haou.business_group_id = p_business_group_id --Bug 4872110
8270: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
8271:
8272: -- Inserts non-associated occupational levels with zero values
8273: insert into per_za_employment_equity
8274: (
8275: report_id,
8276: reporting_date,
8277: business_group_id,

Line 8317: from per_za_employment_equity pzee

8313: , hr_all_organization_units haou
8314: where not exists
8315: (
8316: select 'X'
8317: from per_za_employment_equity pzee
8318: where pzee.level_cat_code = hl.lookup_code
8319: and pzee.report_id = 'EQ4F'
8320: and pzee.business_group_id = p_business_group_id --Bug 4872110
8321: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8340: insert into per_za_employment_equity

8336: -- the twelve months preceding this report)
8337: elsif p_report_code = 'EQ5' then
8338:
8339: -- Populate with New Hires
8340: insert into per_za_employment_equity
8341: (
8342: report_id,
8343: reporting_date,
8344: business_group_id,

Line 8391: 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

8387: paaf.business_group_id,
8388: paei.aei_information7 legal_entity_id,
8389: haou.name legal_entity,
8390: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8391: 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
8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8393: hl.lookup_code meaning_code,
8394: 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,
8395: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073

8388: paei.aei_information7 legal_entity_id,
8389: haou.name legal_entity,
8390: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8391: 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
8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8393: hl.lookup_code meaning_code,
8394: 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,
8395: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8396: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 8394: 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,

8390: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8391: 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
8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8393: hl.lookup_code meaning_code,
8394: 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,
8395: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8396: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8397: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8398: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 8434: 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)

8430: and paei.aei_information7 is not null
8431: and nvl(paei.aei_information6, 'N') <> 'Y'
8432: and haou.organization_id = paei.aei_information7
8433: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8434: 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)
8435: AND hl.lookup_code <> '15' -- Not Applicable.
8436: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8437: and hl1.lookup_code <> '15' -- Not Applicable.
8438: 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)

Line 8438: 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)

8434: 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)
8435: AND hl.lookup_code <> '15' -- Not Applicable.
8436: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8437: and hl1.lookup_code <> '15' -- Not Applicable.
8438: 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)
8439: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8440: -- 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)
8441: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8442: group by paaf.business_group_id,

Line 8440: -- 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)

8436: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8437: and hl1.lookup_code <> '15' -- Not Applicable.
8438: 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)
8439: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8440: -- 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)
8441: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8442: group by paaf.business_group_id,
8443: paei.aei_information7,
8444: haou.name,

Line 8446: 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)),

8442: group by paaf.business_group_id,
8443: paei.aei_information7,
8444: haou.name,
8445: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8446: 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)),
8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8448: hl.lookup_code,
8449: 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'),
8450: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),

8443: paei.aei_information7,
8444: haou.name,
8445: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8446: 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)),
8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8448: hl.lookup_code,
8449: 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'),
8450: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8451: decode(papf.PER_INFORMATION11,null,null,

Line 8449: 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'),

8445: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8446: 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)),
8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8448: hl.lookup_code,
8449: 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'),
8450: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8451: decode(papf.PER_INFORMATION11,null,null,
8452: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8453: -to_char(l_nat_date,'YYYYMMDD'))

Line 8469: insert into per_za_employment_equity

8465:
8466: commit;
8467:
8468: -- Inserts non-associated occupational levels with zero values
8469: insert into per_za_employment_equity
8470: (
8471: report_id,
8472: reporting_date,
8473: business_group_id,

Line 8513: from per_za_employment_equity pzee

8509: , hr_all_organization_units haou
8510: where not exists
8511: (
8512: select 'X'
8513: from per_za_employment_equity pzee
8514: where pzee.level_cat_code = hl.lookup_code
8515: and pzee.report_id = 'EQ5'
8516: and pzee.business_group_id = p_business_group_id --Bug 4872110
8517: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8526: insert into per_za_employment_equity

8522: and haou.business_group_id = p_business_group_id --Bug 4872110
8523: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
8524:
8525: -- Inserts non-associated occupational levels with zero values
8526: insert into per_za_employment_equity
8527: (
8528: report_id,
8529: reporting_date,
8530: business_group_id,

Line 8570: from per_za_employment_equity pzee

8566: , hr_all_organization_units haou
8567: where not exists
8568: (
8569: select 'X'
8570: from per_za_employment_equity pzee
8571: where pzee.level_cat_code = hl.lookup_code
8572: and pzee.report_id = 'EQ5F'
8573: and pzee.business_group_id = p_business_group_id --Bug 4872110
8574: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8590: insert into per_za_employment_equity

8586: -- the twelve months preceding this report)
8587: elsif p_report_code = 'EQ6' then
8588:
8589: -- Populate with Promotions
8590: insert into per_za_employment_equity
8591: (
8592: report_id,
8593: reporting_date,
8594: business_group_id,

Line 8621: 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

8617: paaf.business_group_id,
8618: paei.aei_information7 legal_entity_id,
8619: haou.name legal_entity,
8620: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8621: 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
8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
8623: hl.lookup_code lookup_code,
8624: 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,
8625: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,

8618: paei.aei_information7 legal_entity_id,
8619: haou.name legal_entity,
8620: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8621: 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
8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
8623: hl.lookup_code lookup_code,
8624: 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,
8625: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8626: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 8624: 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,

8620: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8621: 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
8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
8623: hl.lookup_code lookup_code,
8624: 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,
8625: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8626: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8627: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8628: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 8667: 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)

8663: and paei.aei_information7 is not null
8664: and nvl(paei.aei_information6, 'N') <> 'Y'
8665: and haou.organization_id = paei.aei_information7
8666: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8667: 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)
8668: AND hl.lookup_code <> '15' -- Not Applicable.
8669: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8670: and hl1.lookup_code <> '15' -- Not Applicable.
8671: 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)

Line 8671: 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)

8667: 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)
8668: AND hl.lookup_code <> '15' -- Not Applicable.
8669: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8670: and hl1.lookup_code <> '15' -- Not Applicable.
8671: 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)
8672: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8673: -- 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)
8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: 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') <

Line 8673: -- 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)

8669: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8670: and hl1.lookup_code <> '15' -- Not Applicable.
8671: 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)
8672: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8673: -- 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)
8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: 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') <
8676: any
8677: (

Line 8675: 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') <

8671: 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)
8672: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8673: -- 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)
8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: 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') <
8676: any
8677: (
8678: 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
8679: from per_all_assignments_f paaf1

Line 8678: 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

8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: 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') <
8676: any
8677: (
8678: 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
8679: from per_all_assignments_f paaf1
8680: where paaf1.person_id = papf.person_id
8681: and paaf1.primary_flag = 'Y'
8682: 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

Line 8682: 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

8678: 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
8679: from per_all_assignments_f paaf1
8680: where paaf1.person_id = papf.person_id
8681: and paaf1.primary_flag = 'Y'
8682: 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
8683: and paaf1.effective_end_date + 1 = paaf.effective_start_date
8684: )
8685: group by paaf.business_group_id,
8686: paei.aei_information7,

Line 8689: 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)),

8685: group by paaf.business_group_id,
8686: paei.aei_information7,
8687: haou.name,
8688: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8689: 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)),
8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
8691: hl.lookup_code,
8692: 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'),
8693: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),

8686: paei.aei_information7,
8687: haou.name,
8688: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8689: 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)),
8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
8691: hl.lookup_code,
8692: 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'),
8693: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8694: decode(papf.PER_INFORMATION11,null,null,

Line 8692: 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'),

8688: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8689: 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)),
8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
8691: hl.lookup_code,
8692: 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'),
8693: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8694: decode(papf.PER_INFORMATION11,null,null,
8695: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8696: -to_char(l_nat_date,'YYYYMMDD'))

Line 8702: insert into per_za_employment_equity

8698:
8699: commit;
8700:
8701: -- Inserts non-associated occupational levels with zero values
8702: insert into per_za_employment_equity
8703: (
8704: report_id,
8705: reporting_date,
8706: business_group_id,

Line 8746: from per_za_employment_equity pzee

8742: , hr_all_organization_units haou
8743: where not exists
8744: (
8745: select 'X'
8746: from per_za_employment_equity pzee
8747: where pzee.level_cat_code = hl.lookup_code
8748: and pzee.report_id = 'EQ6'
8749: and pzee.business_group_id = p_business_group_id --Bug 4872110
8750: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8759: insert into per_za_employment_equity

8755: and haou.business_group_id = p_business_group_id --Bug 4872110
8756: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
8757:
8758: -- Inserts non-associated occupational levels with zero values
8759: insert into per_za_employment_equity
8760: (
8761: report_id,
8762: reporting_date,
8763: business_group_id,

Line 8803: from per_za_employment_equity pzee

8799: , hr_all_organization_units haou
8800: where not exists
8801: (
8802: select 'X'
8803: from per_za_employment_equity pzee
8804: where pzee.level_cat_code = hl.lookup_code
8805: and pzee.report_id = 'EQ6F'
8806: and pzee.business_group_id = p_business_group_id --Bug 4872110
8807: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 8824: insert into per_za_employment_equity

8820: -- the twelve months preceding this report)
8821: elsif p_report_code = 'EQ7' then
8822:
8823: -- Populate with Terminations
8824: insert into per_za_employment_equity
8825: (
8826: report_id,
8827: reporting_date,
8828: business_group_id,

Line 8876: 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

8872: paaf.business_group_id,
8873: paei.aei_information7 legal_entity_id,
8874: haou.name legal_entity,
8875: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8876: 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
8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
8878: hl.lookup_code meaning_code,
8879: 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,
8880: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

8873: paei.aei_information7 legal_entity_id,
8874: haou.name legal_entity,
8875: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8876: 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
8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
8878: hl.lookup_code meaning_code,
8879: 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,
8880: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8881: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 8879: 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,

8875: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8876: 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
8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
8878: hl.lookup_code meaning_code,
8879: 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,
8880: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8881: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8882: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8883: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 8919: 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)

8915: and paei.aei_information7 is not null
8916: and nvl(paei.aei_information6, 'N') <> 'Y'
8917: and haou.organization_id = paei.aei_information7
8918: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8919: 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)
8920: AND hl.lookup_code <> '15' -- Not Applicable.
8921: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8922: and hl1.lookup_code <> '15' -- Not Applicable
8923: 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)

Line 8923: 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)

8919: 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)
8920: AND hl.lookup_code <> '15' -- Not Applicable.
8921: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8922: and hl1.lookup_code <> '15' -- Not Applicable
8923: 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)
8924: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8925: -- 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)
8926: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8927: group by paaf.business_group_id,

Line 8925: -- 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)

8921: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8922: and hl1.lookup_code <> '15' -- Not Applicable
8923: 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)
8924: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8925: -- 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)
8926: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8927: group by paaf.business_group_id,
8928: paei.aei_information7,
8929: haou.name,

Line 8931: 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)),

8927: group by paaf.business_group_id,
8928: paei.aei_information7,
8929: haou.name,
8930: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8931: 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)),
8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
8933: hl.lookup_code,
8934: 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'),
8935: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),

8928: paei.aei_information7,
8929: haou.name,
8930: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8931: 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)),
8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
8933: hl.lookup_code,
8934: 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'),
8935: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8936: decode(papf.PER_INFORMATION11,null,null,

Line 8934: 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'),

8930: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8931: 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)),
8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
8933: hl.lookup_code,
8934: 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'),
8935: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8936: decode(papf.PER_INFORMATION11,null,null,
8937: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8938: -to_char(l_nat_date,'YYYYMMDD'))

Line 8955: insert into per_za_employment_equity

8951:
8952: commit;
8953:
8954: -- Inserts non-associated occupational levels with zero values
8955: insert into per_za_employment_equity
8956: (
8957: report_id,
8958: reporting_date,
8959: business_group_id,

Line 8999: from per_za_employment_equity pzee

8995: , hr_all_organization_units haou
8996: where not exists
8997: (
8998: select 'X'
8999: from per_za_employment_equity pzee
9000: where pzee.level_cat_code = hl.lookup_code
9001: and pzee.report_id = 'EQ7'
9002: and pzee.business_group_id = p_business_group_id --Bug 4872110
9003: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 9011: insert into per_za_employment_equity

9007: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9008: and haou.business_group_id = p_business_group_id --Bug 4872110
9009: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
9010:
9011: insert into per_za_employment_equity
9012: (
9013: report_id,
9014: reporting_date,
9015: business_group_id,

Line 9055: from per_za_employment_equity pzee

9051: , hr_all_organization_units haou
9052: where not exists
9053: (
9054: select 'X'
9055: from per_za_employment_equity pzee
9056: where pzee.level_cat_code = hl.lookup_code
9057: and pzee.report_id = 'EQ7F'
9058: and pzee.business_group_id = p_business_group_id --Bug 4872110
9059: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 9075: insert into per_za_employment_equity

9071: -- category during the twelve months preceding this report)
9072: elsif p_report_code = 'EQ8' then
9073:
9074: -- Populate with Termination Reason totals
9075: insert into per_za_employment_equity
9076: (
9077: report_id,
9078: reporting_date,
9079: business_group_id,

Line 9145: 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

9141: paaf.business_group_id,
9142: paei.aei_information7 legal_entity_id,
9143: haou.name legal_entity,
9144: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
9145: 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
9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
9147: ppos.leaving_reason meaning_code,
9148: 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,
9149: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

Line 9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073

9142: paei.aei_information7 legal_entity_id,
9143: haou.name legal_entity,
9144: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
9145: 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
9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
9147: ppos.leaving_reason meaning_code,
9148: 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,
9149: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
9150: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 9148: 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,

9144: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
9145: 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
9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
9147: ppos.leaving_reason meaning_code,
9148: 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,
9149: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
9150: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
9151: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
9152: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 9187: 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'

9183: and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
9184: and paei.aei_information7 is not null
9185: and nvl(paei.aei_information6, 'N') <> 'Y'
9186: and haou.organization_id = paei.aei_information7
9187: 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'
9188: 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'
9189: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9190: 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)
9191: and hl.lookup_code <> '15' -- Not Applicable

Line 9188: 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'

9184: and paei.aei_information7 is not null
9185: and nvl(paei.aei_information6, 'N') <> 'Y'
9186: and haou.organization_id = paei.aei_information7
9187: 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'
9188: 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'
9189: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9190: 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)
9191: and hl.lookup_code <> '15' -- Not Applicable
9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'

Line 9190: 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)

9186: and haou.organization_id = paei.aei_information7
9187: 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'
9188: 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'
9189: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9190: 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)
9191: and hl.lookup_code <> '15' -- Not Applicable
9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
9193: and hl1.lookup_code <> '15' -- Not Applicable
9194: 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)

Line 9194: 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)

9190: 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)
9191: and hl.lookup_code <> '15' -- Not Applicable
9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
9193: and hl1.lookup_code <> '15' -- Not Applicable
9194: 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)
9195: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
9196: -- 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)
9197: -- and hl2.lookup_code <> '15' -- Not Applicable
9198: group by paaf.business_group_id,

Line 9196: -- 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)

9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
9193: and hl1.lookup_code <> '15' -- Not Applicable
9194: 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)
9195: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
9196: -- 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)
9197: -- and hl2.lookup_code <> '15' -- Not Applicable
9198: group by paaf.business_group_id,
9199: paei.aei_information7,
9200: haou.name,

Line 9202: 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)),

9198: group by paaf.business_group_id,
9199: paei.aei_information7,
9200: haou.name,
9201: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
9202: 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)),
9203: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
9204: ppos.leaving_reason,
9205: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
9206: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

Line 9203: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),

9199: paei.aei_information7,
9200: haou.name,
9201: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
9202: 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)),
9203: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
9204: ppos.leaving_reason,
9205: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
9206: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
9207: decode(papf.PER_INFORMATION11,null,null,

Line 9205: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),

9201: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
9202: 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)),
9203: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
9204: ppos.leaving_reason,
9205: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
9206: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
9207: decode(papf.PER_INFORMATION11,null,null,
9208: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
9209: -to_char(l_nat_date,'YYYYMMDD'))

Line 9258: insert into per_za_employment_equity

9254: else
9255: l_reason := 'Death';
9256: end if;
9257:
9258: insert into per_za_employment_equity
9259: (
9260: report_id,
9261: reporting_date,
9262: business_group_id,

Line 9319: from per_za_employment_equity pzee

9315: from hr_all_organization_units haou
9316: where not exists
9317: (
9318: select 'X'
9319: from per_za_employment_equity pzee
9320: where pzee.level_cat = l_reason
9321: and pzee.report_id = 'EQ8'
9322: and pzee.business_group_id = p_business_group_id --Bug 4872110
9323: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 9331: insert into per_za_employment_equity

9327: and haou.business_group_id = p_business_group_id --Bug 4872110
9328: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
9329:
9330:
9331: insert into per_za_employment_equity
9332: (
9333: report_id,
9334: reporting_date,
9335: business_group_id,

Line 9390: from per_za_employment_equity pzee

9386: from hr_all_organization_units haou
9387: where not exists
9388: (
9389: select 'X'
9390: from per_za_employment_equity pzee
9391: where pzee.level_cat = l_reason
9392: and pzee.report_id = 'EQ8F'
9393: and pzee.business_group_id = p_business_group_id --Bug 4872110
9394: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 9417: DELETE FROM per_za_employment_equity

9413: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
9414: ) is
9415:
9416: begin
9417: DELETE FROM per_za_employment_equity
9418: Where REPORT_ID IN ('EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8',
9419: 'EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F'
9420: );
9421:

Line 9472: insert into per_za_employment_equity

9468: , p_legal_entity_id =>p_legal_entity_id
9469: );
9470:
9471: -- Inserts non-associated occupational categories with zero values
9472: insert into per_za_employment_equity
9473: (
9474: report_id,
9475: reporting_date,
9476: business_group_id,

Line 9511: from per_za_employment_equity pzee1

9507: 0,
9508: 0,
9509: 0,
9510: 0
9511: from per_za_employment_equity pzee1
9512: Where pzee1.business_group_id = p_business_group_id
9513: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
9514: AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
9515: AND not exists

Line 9518: from per_za_employment_equity pzee

9514: AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
9515: AND not exists
9516: (
9517: select 'X'
9518: from per_za_employment_equity pzee
9519: where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
9520: AND pzee.legal_entity_id = pzee1.legal_entity_id
9521: AND pzee.report_id ||'F' = pzee1.report_id
9522: AND pzee1.level_cat_code = pzee.level_cat_code

Line 9530: insert into per_za_employment_equity

9526: );
9527:
9528:
9529: -- Inserts non-associated occupational categories with zero values
9530: insert into per_za_employment_equity
9531: (
9532: report_id,
9533: reporting_date,
9534: business_group_id,

Line 9569: from per_za_employment_equity pzee1

9565: 0,
9566: 0,
9567: 0,
9568: 0
9569: from per_za_employment_equity pzee1
9570: Where pzee1.business_group_id = p_business_group_id
9571: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
9572: AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
9573: AND not exists

Line 9576: from per_za_employment_equity pzee

9572: AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
9573: AND not exists
9574: (
9575: select 'X'
9576: from per_za_employment_equity pzee
9577: where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
9578: AND pzee.legal_entity_id = pzee1.legal_entity_id
9579: AND pzee1.report_id ||'F' = pzee.report_id
9580: AND pzee1.level_cat_code = pzee.level_cat_code

Line 9591: end per_za_employment_equity_pkg; -- package body

9587:
9588:
9589: End populate_ee_table_EEWF_new;
9590:
9591: end per_za_employment_equity_pkg; -- package body