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.30.12010000.7 2008/09/01 09:06:03 rbabla ship $ */
3: /*
4: ==============================================================================
5: This package loads data into table per_za_employment_equity for use by

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.30.12010000.7 2008/09/01 09:06:03 rbabla ship $ */
3: /*
4: ==============================================================================
5: This package loads data into table per_za_employment_equity for use by
6: the Employment Equity Reports
7:
8: MODIFICATION HISTORY
9:

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

125:
126: TYPE t_E_differential IS TABLE OF r_Encome_diff_rec INDEX BY binary_integer;
127:
128: -- Global variables
129: g_package constant varchar2(30) := 'per_za_employment_equity_pkg.';
130: g_assignments_table t_assignments;
131: g_cat_averages_table t_averages;
132: g_lev_averages_table t_averages;
133:

Line 521: DELETE FROM per_za_employment_equity

517: g_cat_averages_table.delete;
518: g_lev_averages_table.delete;
519: g_cat_Enc_Diff_table.delete;
520: g_lev_Enc_Diff_table.delete;
521: DELETE FROM per_za_employment_equity
522: Where report_id IN
523: ( 'ED1', 'ED2', 'ED1I','ED2I');
524:
525: end reset_tables;

Line 668: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,

664: select paaf.assignment_id,
665: paaf.person_id, -- Bug 4413678
666: paaf.payroll_id,
667: paei.aei_information7,
668: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
669: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
670: paaf.pay_basis_id
671: from per_assignment_extra_info paei,
672: per_assignment_status_types past,

Line 669: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,

665: paaf.person_id, -- Bug 4413678
666: paaf.payroll_id,
667: paei.aei_information7,
668: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
669: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
670: paaf.pay_basis_id
671: from per_assignment_extra_info paei,
672: per_assignment_status_types past,
673: per_all_assignments_f paaf

Line 1761: insert into per_za_employment_equity

1757: if p_report_code = 'EQ1' then
1758:
1759: -- Note: The date effective select on per_all_assignments_f is ok in this case, since an assignment
1760: -- record always exist at the same time as an employee record with status EMP
1761: insert into per_za_employment_equity
1762: (
1763: report_id,
1764: reporting_date,
1765: business_group_id,

Line 1793: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

1789: paaf.business_group_id,
1790: paei.aei_information7 legal_entity_id,
1791: haou.name legal_entity,
1792: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1793: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1794: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1795: hl.lookup_code meaning_code,
1796: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1797: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

1790: paei.aei_information7 legal_entity_id,
1791: haou.name legal_entity,
1792: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1793: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1794: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1795: hl.lookup_code meaning_code,
1796: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1797: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1798: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 1796: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,

1792: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1793: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1794: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1795: hl.lookup_code meaning_code,
1796: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1797: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1798: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
1799: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
1800: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 1833: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

1829: and paei.aei_information7 is not null
1830: and nvl(paei.aei_information6, 'N') <> 'Y'
1831: and haou.organization_id = paei.aei_information7
1832: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
1833: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1834: AND hl.lookup_code <> '15' -- Not Applicable.
1835: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1836: AND hl1.lookup_code <> '15' -- Not Applicable.
1837: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 1837: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

1833: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1834: AND hl.lookup_code <> '15' -- Not Applicable.
1835: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1836: AND hl1.lookup_code <> '15' -- Not Applicable.
1837: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1838: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1839: AND hl2.lookup_code <> '15' -- Not Applicable.
1840: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1841: group by paaf.business_group_id,

Line 1840: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

1836: AND hl1.lookup_code <> '15' -- Not Applicable.
1837: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1838: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1839: AND hl2.lookup_code <> '15' -- Not Applicable.
1840: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1841: group by paaf.business_group_id,
1842: paei.aei_information7,
1843: haou.name,
1844: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073

Line 1845: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073

1841: group by paaf.business_group_id,
1842: paei.aei_information7,
1843: haou.name,
1844: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1845: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1846: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1847: hl.lookup_code,
1848: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1849: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

1842: paei.aei_information7,
1843: haou.name,
1844: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1845: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1846: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1847: hl.lookup_code,
1848: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1849: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1850: decode(papf.PER_INFORMATION3,null,null,

Line 1848: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),

1844: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1845: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1846: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1847: hl.lookup_code,
1848: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1849: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1850: decode(papf.PER_INFORMATION3,null,null,
1851: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
1852: -to_char(p_report_date,'YYYYMMDD'))

Line 1859: insert into per_za_employment_equity

1855:
1856: commit;
1857:
1858: -- Inserts non-associated occupational categories with zero values
1859: insert into per_za_employment_equity
1860: (
1861: report_id,
1862: reporting_date,
1863: business_group_id,

Line 1903: from per_za_employment_equity pzee

1899: , hr_all_organization_units haou
1900: where not exists
1901: (
1902: select 'X'
1903: from per_za_employment_equity pzee
1904: where pzee.level_cat_code = hl.lookup_code
1905: and pzee.report_id = 'EQ1'
1906: and pzee.business_group_id = p_business_group_id --Bug 4872110
1907: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 1916: insert into per_za_employment_equity

1912: and haou.business_group_id = p_business_group_id --Bug 4872110
1913: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
1914:
1915: -- Inseting 0 VALUES FOR FOREIGN nationals
1916: insert into per_za_employment_equity
1917: (
1918: report_id,
1919: reporting_date,
1920: business_group_id,

Line 1960: from per_za_employment_equity pzee

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

Line 1980: insert into per_za_employment_equity

1976: -- 5. Occupational Levels (only employees with disabilities)
1977: elsif p_report_code = 'EQ2' then
1978:
1979: -- Populate with Occupational Level Totals
1980: insert into per_za_employment_equity
1981: (
1982: report_id,
1983: reporting_date,
1984: business_group_id,

Line 2012: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

2008: paaf.business_group_id,
2009: paei.aei_information7 legal_entity_id,
2010: haou.name legal_entity,
2011: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2012: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2013: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2014: hl.lookup_code meaning_code,
2015: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2016: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2009: paei.aei_information7 legal_entity_id,
2010: haou.name legal_entity,
2011: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2012: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2013: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2014: hl.lookup_code meaning_code,
2015: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2016: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2017: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2015: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,

2011: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2012: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2013: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2014: hl.lookup_code meaning_code,
2015: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2016: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2017: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2018: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2019: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2052: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2048: and paei.aei_information7 is not null
2049: and nvl(paei.aei_information6, 'N') <> 'Y'
2050: and haou.organization_id = paei.aei_information7
2051: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2052: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2053: AND hl.lookup_code <> '15' -- Not Applicable.
2054: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2055: AND hl1.lookup_code <> '15' -- Operation / core function
2056: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 2056: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2052: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2053: AND hl.lookup_code <> '15' -- Not Applicable.
2054: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2055: AND hl1.lookup_code <> '15' -- Operation / core function
2056: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2057: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2058: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2059: AND hl2.lookup_code <> '15' -- Not Applicable.
2060: group by paaf.business_group_id,

Line 2058: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2054: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2055: AND hl1.lookup_code <> '15' -- Operation / core function
2056: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2057: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2058: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2059: AND hl2.lookup_code <> '15' -- Not Applicable.
2060: group by paaf.business_group_id,
2061: paei.aei_information7,
2062: haou.name,

Line 2064: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073

2060: group by paaf.business_group_id,
2061: paei.aei_information7,
2062: haou.name,
2063: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2064: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2065: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2066: hl.lookup_code,
2067: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2068: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2061: paei.aei_information7,
2062: haou.name,
2063: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2064: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2065: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2066: hl.lookup_code,
2067: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2068: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2069: decode(papf.PER_INFORMATION3,null,null,

Line 2067: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),

2063: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2064: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2065: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2066: hl.lookup_code,
2067: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2068: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2069: decode(papf.PER_INFORMATION3,null,null,
2070: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2071: -to_char(p_report_date,'YYYYMMDD'))

Line 2077: insert into per_za_employment_equity

2073:
2074: commit;
2075:
2076: -- Inserts non-associated occupational levels with zero values
2077: insert into per_za_employment_equity
2078: (
2079: report_id,
2080: reporting_date,
2081: business_group_id,

Line 2121: from per_za_employment_equity pzee

2117: , hr_all_organization_units haou
2118: where not exists
2119: (
2120: select 'X'
2121: from per_za_employment_equity pzee
2122: where pzee.level_cat_code = hl.lookup_code
2123: and pzee.report_id = 'EQ2'
2124: and pzee.business_group_id = p_business_group_id --Bug 4872110
2125: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2135: insert into per_za_employment_equity

2131: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2132:
2133:
2134: -- inserting 0 values for the Foreign Nationals
2135: insert into per_za_employment_equity
2136: (
2137: report_id,
2138: reporting_date,
2139: business_group_id,

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 = 'EQ2F'
2182: and pzee.business_group_id = p_business_group_id --Bug 4872110
2183: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2198: insert into per_za_employment_equity

2194: -- For employment equity enhancement
2195: elsif p_report_code = 'EQ3' then
2196:
2197: -- Populate with New Hires
2198: insert into per_za_employment_equity
2199: (
2200: report_id,
2201: reporting_date,
2202: business_group_id,

Line 2249: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

2245: paaf.business_group_id,
2246: paei.aei_information7 legal_entity_id,
2247: haou.name legal_entity,
2248: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2249: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2250: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2251: hl.lookup_code meaning_code,
2252: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2253: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2246: paei.aei_information7 legal_entity_id,
2247: haou.name legal_entity,
2248: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2249: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2250: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2251: hl.lookup_code meaning_code,
2252: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2253: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2254: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2252: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,

2248: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2249: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2250: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2251: hl.lookup_code meaning_code,
2252: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2253: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2254: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2255: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2256: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2289: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2285: and paei.aei_information7 is not null
2286: and nvl(paei.aei_information6, 'N') <> 'Y'
2287: and haou.organization_id = paei.aei_information7
2288: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2289: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2290: AND hl.lookup_code <> '15' -- Not Applicable.
2291: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2292: AND hl1.lookup_code = '1' -- Operation / core function
2293: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 2293: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2289: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2290: AND hl.lookup_code <> '15' -- Not Applicable.
2291: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2292: AND hl1.lookup_code = '1' -- Operation / core function
2293: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2294: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2295: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2296: AND hl2.lookup_code <> '15' -- Not Applicable.
2297: group by paaf.business_group_id,

Line 2295: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2291: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2292: AND hl1.lookup_code = '1' -- Operation / core function
2293: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2294: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2295: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2296: AND hl2.lookup_code <> '15' -- Not Applicable.
2297: group by paaf.business_group_id,
2298: paei.aei_information7,
2299: haou.name,

Line 2301: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),

2297: group by paaf.business_group_id,
2298: paei.aei_information7,
2299: haou.name,
2300: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2301: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2302: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2303: hl.lookup_code,
2304: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2305: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2298: paei.aei_information7,
2299: haou.name,
2300: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2301: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2302: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2303: hl.lookup_code,
2304: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2305: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2306: decode(papf.PER_INFORMATION3,null,null,

Line 2304: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),

2300: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2301: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2302: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2303: hl.lookup_code,
2304: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2305: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2306: decode(papf.PER_INFORMATION3,null,null,
2307: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2308: -to_char(p_report_date,'YYYYMMDD'))

Line 2324: insert into per_za_employment_equity

2320:
2321: commit;
2322:
2323: -- Inserts non-associated occupational levels with zero values
2324: insert into per_za_employment_equity
2325: (
2326: report_id,
2327: reporting_date,
2328: business_group_id,

Line 2368: from per_za_employment_equity pzee

2364: , hr_all_organization_units haou
2365: where not exists
2366: (
2367: select 'X'
2368: from per_za_employment_equity pzee
2369: where pzee.level_cat_code = hl.lookup_code
2370: and pzee.report_id = 'EQ3'
2371: and pzee.business_group_id = p_business_group_id --Bug 4872110
2372: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2381: insert into per_za_employment_equity

2377: and haou.business_group_id = p_business_group_id --Bug 4872110
2378: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2379:
2380: -- Inserts non-associated occupational levels with zero values
2381: insert into per_za_employment_equity
2382: (
2383: report_id,
2384: reporting_date,
2385: business_group_id,

Line 2425: from per_za_employment_equity pzee

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

Line 2445: insert into per_za_employment_equity

2441: -- the twelve months preceding this report)
2442: elsif p_report_code = 'EQ4' then
2443:
2444: -- Populate with New Hires
2445: insert into per_za_employment_equity
2446: (
2447: report_id,
2448: reporting_date,
2449: business_group_id,

Line 2496: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

2492: paaf.business_group_id,
2493: paei.aei_information7 legal_entity_id,
2494: haou.name legal_entity,
2495: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2496: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2497: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2498: hl.lookup_code meaning_code,
2499: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2500: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2493: paei.aei_information7 legal_entity_id,
2494: haou.name legal_entity,
2495: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2496: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2497: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2498: hl.lookup_code meaning_code,
2499: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2500: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2501: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2499: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,

2495: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2496: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2497: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2498: hl.lookup_code meaning_code,
2499: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2500: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2501: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2502: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2503: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2536: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2532: and paei.aei_information7 is not null
2533: and nvl(paei.aei_information6, 'N') <> 'Y'
2534: and haou.organization_id = paei.aei_information7
2535: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2536: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2537: AND hl.lookup_code <> '15' -- Not Applicable.
2538: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2539: AND hl1.lookup_code = '2' -- Support function
2540: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 2540: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2536: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2537: AND hl.lookup_code <> '15' -- Not Applicable.
2538: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2539: AND hl1.lookup_code = '2' -- Support function
2540: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2541: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2542: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2543: AND hl2.lookup_code <> '15' -- Not Applicable.
2544: group by paaf.business_group_id,

Line 2542: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2538: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2539: AND hl1.lookup_code = '2' -- Support function
2540: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2541: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2542: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2543: AND hl2.lookup_code <> '15' -- Not Applicable.
2544: group by paaf.business_group_id,
2545: paei.aei_information7,
2546: haou.name,

Line 2548: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),

2544: group by paaf.business_group_id,
2545: paei.aei_information7,
2546: haou.name,
2547: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2548: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2549: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2550: hl.lookup_code,
2551: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2552: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2545: paei.aei_information7,
2546: haou.name,
2547: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2548: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2549: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2550: hl.lookup_code,
2551: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2552: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2553: decode(papf.PER_INFORMATION3,null,null,

Line 2551: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),

2547: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2548: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2549: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2550: hl.lookup_code,
2551: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2552: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2553: decode(papf.PER_INFORMATION3,null,null,
2554: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2555: -to_char(p_report_date,'YYYYMMDD'))

Line 2571: insert into per_za_employment_equity

2567:
2568: commit;
2569:
2570: -- Inserts non-associated occupational levels with zero values
2571: insert into per_za_employment_equity
2572: (
2573: report_id,
2574: reporting_date,
2575: business_group_id,

Line 2615: from per_za_employment_equity pzee

2611: , hr_all_organization_units haou
2612: where not exists
2613: (
2614: select 'X'
2615: from per_za_employment_equity pzee
2616: where pzee.level_cat_code = hl.lookup_code
2617: and pzee.report_id = 'EQ4'
2618: and pzee.business_group_id = p_business_group_id --Bug 4872110
2619: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2628: insert into per_za_employment_equity

2624: and haou.business_group_id = p_business_group_id --Bug 4872110
2625: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2626:
2627: -- Inserts non-associated occupational levels with zero values
2628: insert into per_za_employment_equity
2629: (
2630: report_id,
2631: reporting_date,
2632: business_group_id,

Line 2672: from per_za_employment_equity pzee

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

Line 2695: insert into per_za_employment_equity

2691: -- the twelve months preceding this report)
2692: elsif p_report_code = 'EQ5' then
2693:
2694: -- Populate with New Hires
2695: insert into per_za_employment_equity
2696: (
2697: report_id,
2698: reporting_date,
2699: business_group_id,

Line 2746: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

2742: paaf.business_group_id,
2743: paei.aei_information7 legal_entity_id,
2744: haou.name legal_entity,
2745: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2746: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2747: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2748: hl.lookup_code meaning_code,
2749: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2750: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2743: paei.aei_information7 legal_entity_id,
2744: haou.name legal_entity,
2745: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2746: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2747: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2748: hl.lookup_code meaning_code,
2749: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2750: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2751: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2749: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,

2745: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2746: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2747: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2748: hl.lookup_code meaning_code,
2749: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2750: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2751: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2752: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2753: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 2789: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2785: and paei.aei_information7 is not null
2786: and nvl(paei.aei_information6, 'N') <> 'Y'
2787: and haou.organization_id = paei.aei_information7
2788: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2789: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2790: AND hl.lookup_code <> '15' -- Not Applicable.
2791: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2792: and hl1.lookup_code <> '15' -- Not Applicable.
2793: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 2793: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2789: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2790: AND hl.lookup_code <> '15' -- Not Applicable.
2791: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2792: and hl1.lookup_code <> '15' -- Not Applicable.
2793: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2794: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2795: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2796: AND hl2.lookup_code <> '15' -- Not Applicable.
2797: group by paaf.business_group_id,

Line 2795: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

2791: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2792: and hl1.lookup_code <> '15' -- Not Applicable.
2793: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2794: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2795: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2796: AND hl2.lookup_code <> '15' -- Not Applicable.
2797: group by paaf.business_group_id,
2798: paei.aei_information7,
2799: haou.name,

Line 2801: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),

2797: group by paaf.business_group_id,
2798: paei.aei_information7,
2799: haou.name,
2800: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2801: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2802: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2803: hl.lookup_code,
2804: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2805: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2798: paei.aei_information7,
2799: haou.name,
2800: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2801: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2802: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2803: hl.lookup_code,
2804: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2805: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2806: decode(papf.PER_INFORMATION3,null,null,

Line 2804: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),

2800: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2801: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2802: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2803: hl.lookup_code,
2804: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2805: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2806: decode(papf.PER_INFORMATION3,null,null,
2807: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2808: -to_char(p_report_date,'YYYYMMDD'))

Line 2824: insert into per_za_employment_equity

2820:
2821: commit;
2822:
2823: -- Inserts non-associated occupational levels with zero values
2824: insert into per_za_employment_equity
2825: (
2826: report_id,
2827: reporting_date,
2828: business_group_id,

Line 2868: from per_za_employment_equity pzee

2864: , hr_all_organization_units haou
2865: where not exists
2866: (
2867: select 'X'
2868: from per_za_employment_equity pzee
2869: where pzee.level_cat_code = hl.lookup_code
2870: and pzee.report_id = 'EQ5'
2871: and pzee.business_group_id = p_business_group_id --Bug 4872110
2872: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 2881: insert into per_za_employment_equity

2877: and haou.business_group_id = p_business_group_id --Bug 4872110
2878: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
2879:
2880: -- Inserts non-associated occupational levels with zero values
2881: insert into per_za_employment_equity
2882: (
2883: report_id,
2884: reporting_date,
2885: business_group_id,

Line 2925: from per_za_employment_equity pzee

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

Line 2945: insert into per_za_employment_equity

2941: -- the twelve months preceding this report)
2942: elsif p_report_code = 'EQ6' then
2943:
2944: -- Populate with Promotions
2945: insert into per_za_employment_equity
2946: (
2947: report_id,
2948: reporting_date,
2949: business_group_id,

Line 2976: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

2972: paaf.business_group_id,
2973: paei.aei_information7 legal_entity_id,
2974: haou.name legal_entity,
2975: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2976: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2977: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
2978: hl.lookup_code lookup_code,
2979: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2980: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2973: paei.aei_information7 legal_entity_id,
2974: haou.name legal_entity,
2975: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2976: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2977: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
2978: hl.lookup_code lookup_code,
2979: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2980: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2981: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 2979: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,

2975: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2976: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2977: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
2978: hl.lookup_code lookup_code,
2979: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2980: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2981: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2982: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2983: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 3022: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3018: and paei.aei_information7 is not null
3019: and nvl(paei.aei_information6, 'N') <> 'Y'
3020: and haou.organization_id = paei.aei_information7
3021: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3022: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3023: AND hl.lookup_code <> '15' -- Not Applicable.
3024: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3025: and hl1.lookup_code <> '15' -- Not Applicable.
3026: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 3026: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3022: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3023: AND hl.lookup_code <> '15' -- Not Applicable.
3024: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3025: and hl1.lookup_code <> '15' -- Not Applicable.
3026: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3027: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3028: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3029: AND hl2.lookup_code <> '15' -- Not Applicable.
3030: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <

Line 3028: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3024: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3025: and hl1.lookup_code <> '15' -- Not Applicable.
3026: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3027: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3028: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3029: AND hl2.lookup_code <> '15' -- Not Applicable.
3030: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3031: any
3032: (

Line 3030: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <

3026: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3027: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3028: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3029: AND hl2.lookup_code <> '15' -- Not Applicable.
3030: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3031: any
3032: (
3033: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3034: from per_all_assignments_f paaf1

Line 3033: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code

3029: AND hl2.lookup_code <> '15' -- Not Applicable.
3030: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3031: any
3032: (
3033: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3034: from per_all_assignments_f paaf1
3035: where paaf1.person_id = papf.person_id
3036: and paaf1.primary_flag = 'Y'
3037: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null

Line 3037: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null

3033: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3034: from per_all_assignments_f paaf1
3035: where paaf1.person_id = papf.person_id
3036: and paaf1.primary_flag = 'Y'
3037: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null
3038: and paaf1.effective_end_date + 1 = paaf.effective_start_date
3039: )
3040: group by paaf.business_group_id,
3041: paei.aei_information7,

Line 3044: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),

3040: group by paaf.business_group_id,
3041: paei.aei_information7,
3042: haou.name,
3043: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3044: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3045: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3046: hl.lookup_code,
3047: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3048: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

3041: paei.aei_information7,
3042: haou.name,
3043: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3044: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3045: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3046: hl.lookup_code,
3047: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3048: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3049: decode(papf.PER_INFORMATION3,null,null,

Line 3047: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),

3043: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3044: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3045: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3046: hl.lookup_code,
3047: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3048: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3049: decode(papf.PER_INFORMATION3,null,null,
3050: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3051: -to_char(p_report_date,'YYYYMMDD'))

Line 3057: insert into per_za_employment_equity

3053:
3054: commit;
3055:
3056: -- Inserts non-associated occupational levels with zero values
3057: insert into per_za_employment_equity
3058: (
3059: report_id,
3060: reporting_date,
3061: business_group_id,

Line 3101: from per_za_employment_equity pzee

3097: , hr_all_organization_units haou
3098: where not exists
3099: (
3100: select 'X'
3101: from per_za_employment_equity pzee
3102: where pzee.level_cat_code = hl.lookup_code
3103: and pzee.report_id = 'EQ6'
3104: and pzee.business_group_id = p_business_group_id --Bug 4872110
3105: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3114: insert into per_za_employment_equity

3110: and haou.business_group_id = p_business_group_id --Bug 4872110
3111: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3112:
3113: -- Inserts non-associated occupational levels with zero values
3114: insert into per_za_employment_equity
3115: (
3116: report_id,
3117: reporting_date,
3118: business_group_id,

Line 3158: from per_za_employment_equity pzee

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

Line 3179: insert into per_za_employment_equity

3175: -- the twelve months preceding this report)
3176: elsif p_report_code = 'EQ7' then
3177:
3178: -- Populate with Terminations
3179: insert into per_za_employment_equity
3180: (
3181: report_id,
3182: reporting_date,
3183: business_group_id,

Line 3231: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

3227: paaf.business_group_id,
3228: paei.aei_information7 legal_entity_id,
3229: haou.name legal_entity,
3230: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3231: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3232: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3233: hl.lookup_code meaning_code,
3234: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3235: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

3228: paei.aei_information7 legal_entity_id,
3229: haou.name legal_entity,
3230: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3231: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3232: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3233: hl.lookup_code meaning_code,
3234: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3235: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3236: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

Line 3234: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,

3230: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3231: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3232: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3233: hl.lookup_code meaning_code,
3234: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3235: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3236: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3237: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3238: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 3274: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3270: and paei.aei_information7 is not null
3271: and nvl(paei.aei_information6, 'N') <> 'Y'
3272: and haou.organization_id = paei.aei_information7
3273: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3274: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3275: AND hl.lookup_code <> '15' -- Not Applicable.
3276: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3277: and hl1.lookup_code <> '15' -- Not Applicable
3278: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 3278: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3274: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3275: AND hl.lookup_code <> '15' -- Not Applicable.
3276: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3277: and hl1.lookup_code <> '15' -- Not Applicable
3278: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3279: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3280: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3281: AND hl2.lookup_code <> '15' -- Not Applicable.
3282: group by paaf.business_group_id,

Line 3280: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3276: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3277: and hl1.lookup_code <> '15' -- Not Applicable
3278: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3279: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3280: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3281: AND hl2.lookup_code <> '15' -- Not Applicable.
3282: group by paaf.business_group_id,
3283: paei.aei_information7,
3284: haou.name,

Line 3286: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),

3282: group by paaf.business_group_id,
3283: paei.aei_information7,
3284: haou.name,
3285: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3286: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3287: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3288: hl.lookup_code,
3289: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3290: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

3283: paei.aei_information7,
3284: haou.name,
3285: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3286: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3287: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3288: hl.lookup_code,
3289: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3290: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3291: decode(papf.PER_INFORMATION3,null,null,

Line 3289: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),

3285: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3286: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3287: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3288: hl.lookup_code,
3289: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3290: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3291: decode(papf.PER_INFORMATION3,null,null,
3292: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3293: -to_char(p_report_date,'YYYYMMDD'))

Line 3310: insert into per_za_employment_equity

3306:
3307: commit;
3308:
3309: -- Inserts non-associated occupational levels with zero values
3310: insert into per_za_employment_equity
3311: (
3312: report_id,
3313: reporting_date,
3314: business_group_id,

Line 3354: from per_za_employment_equity pzee

3350: , hr_all_organization_units haou
3351: where not exists
3352: (
3353: select 'X'
3354: from per_za_employment_equity pzee
3355: where pzee.level_cat_code = hl.lookup_code
3356: and pzee.report_id = 'EQ7'
3357: and pzee.business_group_id = p_business_group_id --Bug 4872110
3358: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3366: insert into per_za_employment_equity

3362: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3363: and haou.business_group_id = p_business_group_id --Bug 4872110
3364: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3365:
3366: insert into per_za_employment_equity
3367: (
3368: report_id,
3369: reporting_date,
3370: business_group_id,

Line 3410: from per_za_employment_equity pzee

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

Line 3430: insert into per_za_employment_equity

3426: -- category during the twelve months preceding this report)
3427: elsif p_report_code = 'EQ8' then
3428:
3429: -- Populate with Termination Reason totals
3430: insert into per_za_employment_equity
3431: (
3432: report_id,
3433: reporting_date,
3434: business_group_id,

Line 3497: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073

3493: paaf.business_group_id,
3494: paei.aei_information7 legal_entity_id,
3495: haou.name legal_entity,
3496: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3497: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3498: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3499: ppos.leaving_reason meaning_code,
3500: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3501: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

3494: paei.aei_information7 legal_entity_id,
3495: haou.name legal_entity,
3496: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3497: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3498: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3499: ppos.leaving_reason meaning_code,
3500: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3501: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3502: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

3496: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3497: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3498: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3499: ppos.leaving_reason meaning_code,
3500: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
3501: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3502: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3503: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3504: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

Line 3539: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'

3535: and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
3536: and paei.aei_information7 is not null
3537: and nvl(paei.aei_information6, 'N') <> 'Y'
3538: and haou.organization_id = paei.aei_information7
3539: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3540: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3541: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3542: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3543: and hl.lookup_code <> '15' -- Not Applicable

Line 3540: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'

3536: and paei.aei_information7 is not null
3537: and nvl(paei.aei_information6, 'N') <> 'Y'
3538: and haou.organization_id = paei.aei_information7
3539: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3540: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3541: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3542: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3543: and hl.lookup_code <> '15' -- Not Applicable
3544: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'

Line 3542: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3538: and haou.organization_id = paei.aei_information7
3539: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3540: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3541: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3542: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3543: and hl.lookup_code <> '15' -- Not Applicable
3544: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3545: and hl1.lookup_code <> '15' -- Not Applicable
3546: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

Line 3546: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3542: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3543: and hl.lookup_code <> '15' -- Not Applicable
3544: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3545: and hl1.lookup_code <> '15' -- Not Applicable
3546: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3547: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3548: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3549: and hl2.lookup_code <> '15' -- Not Applicable
3550: group by paaf.business_group_id,

Line 3548: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

3544: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3545: and hl1.lookup_code <> '15' -- Not Applicable
3546: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3547: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3548: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3549: and hl2.lookup_code <> '15' -- Not Applicable
3550: group by paaf.business_group_id,
3551: paei.aei_information7,
3552: haou.name,

Line 3554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),

3550: group by paaf.business_group_id,
3551: paei.aei_information7,
3552: haou.name,
3553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3556: ppos.leaving_reason,
3557: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3558: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

3551: paei.aei_information7,
3552: haou.name,
3553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3556: ppos.leaving_reason,
3557: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3558: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3559: decode(papf.PER_INFORMATION3,null,null,

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

3553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3556: ppos.leaving_reason,
3557: nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason'),
3558: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3559: decode(papf.PER_INFORMATION3,null,null,
3560: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3561: -to_char(p_report_date,'YYYYMMDD'))

Line 3595: insert into per_za_employment_equity

3591: else
3592: l_reason := 'Other';
3593: end if;
3594:
3595: insert into per_za_employment_equity
3596: (
3597: report_id,
3598: reporting_date,
3599: business_group_id,

Line 3653: from per_za_employment_equity pzee

3649: from hr_all_organization_units haou
3650: where not exists
3651: (
3652: select 'X'
3653: from per_za_employment_equity pzee
3654: where pzee.level_cat = l_reason
3655: and pzee.report_id = 'EQ8'
3656: and pzee.business_group_id = p_business_group_id --Bug 4872110
3657: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3665: insert into per_za_employment_equity

3661: and haou.business_group_id = p_business_group_id --Bug 4872110
3662: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
3663:
3664:
3665: insert into per_za_employment_equity
3666: (
3667: report_id,
3668: reporting_date,
3669: business_group_id,

Line 3723: from per_za_employment_equity pzee

3719: from hr_all_organization_units haou
3720: where not exists
3721: (
3722: select 'X'
3723: from per_za_employment_equity pzee
3724: where pzee.level_cat = l_reason
3725: and pzee.report_id = 'EQ8F'
3726: and pzee.business_group_id = p_business_group_id --Bug 4872110
3727: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 3895: DELETE FROM per_za_employment_equity

3891: p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
3892: ) is
3893:
3894: begin
3895: DELETE FROM per_za_employment_equity
3896: Where REPORT_ID IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8',
3897: 'EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F'
3898: );
3899:

Line 3957: insert into per_za_employment_equity

3953: , p_legal_entity_id =>p_legal_entity_id
3954: );
3955:
3956: -- Inserts non-associated occupational categories with zero values
3957: insert into per_za_employment_equity
3958: (
3959: report_id,
3960: reporting_date,
3961: business_group_id,

Line 3996: from per_za_employment_equity pzee1

3992: 0,
3993: 0,
3994: 0,
3995: 0
3996: from per_za_employment_equity pzee1
3997: Where pzee1.business_group_id = p_business_group_id
3998: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
3999: AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
4000: AND not exists

Line 4003: from per_za_employment_equity pzee

3999: AND pzee1.report_id IN ('EQ1F','EQ2F','EQ3F','EQ4F','EQ5F','EQ6F','EQ7F','EQ8F')
4000: AND not exists
4001: (
4002: select 'X'
4003: from per_za_employment_equity pzee
4004: where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
4005: AND pzee.legal_entity_id = pzee1.legal_entity_id
4006: AND pzee.report_id ||'F' = pzee1.report_id
4007: AND pzee1.level_cat_code = pzee.level_cat_code

Line 4015: insert into per_za_employment_equity

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

Line 4054: from per_za_employment_equity pzee1

4050: 0,
4051: 0,
4052: 0,
4053: 0
4054: from per_za_employment_equity pzee1
4055: Where pzee1.business_group_id = p_business_group_id
4056: AND pzee1.legal_entity_id = nvl(p_legal_entity_id, pzee1.legal_entity_id)
4057: AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
4058: AND not exists

Line 4061: from per_za_employment_equity pzee

4057: AND pzee1.report_id IN ('EQ1','EQ2','EQ3','EQ4','EQ5','EQ6','EQ7','EQ8')
4058: AND not exists
4059: (
4060: select 'X'
4061: from per_za_employment_equity pzee
4062: where pzee.business_group_id = pzee1.business_group_id --Bug 4872110
4063: AND pzee.legal_entity_id = pzee1.legal_entity_id
4064: AND pzee1.report_id ||'F' = pzee.report_id
4065: AND pzee1.level_cat_code = pzee.level_cat_code

Line 4524: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,

4520: paaf.payroll_id,
4521: paei.aei_information7 ,
4522: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4523: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4524: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4525: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4526: paaf.pay_basis_id
4527: from per_assignment_extra_info paei,
4528: per_assignment_status_types past,

Line 4525: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,

4521: paei.aei_information7 ,
4522: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4523: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4524: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4525: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4526: paaf.pay_basis_id
4527: from per_assignment_extra_info paei,
4528: per_assignment_status_types past,
4529: per_all_assignments_f paaf,

Line 4550: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

4546: AND hl_fn.lookup_code <>'15'
4547: AND hl_cat.application_id = '800'
4548: AND hl_lev.application_id = '800'
4549: AND hl_fn.application_id = '800'
4550: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4551: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4552: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4553: and nvl(paei.aei_information6, 'N') <> 'Y'
4554: order BY paei.aei_information7, paaf.payroll_id;

Line 4551: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

4547: AND hl_cat.application_id = '800'
4548: AND hl_lev.application_id = '800'
4549: AND hl_fn.application_id = '800'
4550: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4551: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4552: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4553: and nvl(paei.aei_information6, 'N') <> 'Y'
4554: order BY paei.aei_information7, paaf.payroll_id;
4555:

Line 4552: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

4548: AND hl_lev.application_id = '800'
4549: AND hl_fn.application_id = '800'
4550: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4551: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4552: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4553: and nvl(paei.aei_information6, 'N') <> 'Y'
4554: order BY paei.aei_information7, paaf.payroll_id;
4555:
4556: l_old_payroll_id per_all_assignments_f.payroll_id%type := -9999;

Line 5000: INSERT INTO per_za_employment_equity

4996:
4997: l_rowind := g_cat_Enc_Diff_table.first;
4998: loop
4999: exit when l_rowind is null;
5000: INSERT INTO per_za_employment_equity
5001: (
5002: report_id,
5003: reporting_date,
5004: business_group_id,

Line 5043: INSERT INTO per_za_employment_equity

5039: FROM hr_all_organization_units haou
5040: Where haou.organization_id = g_cat_Enc_Diff_table(l_rowind).legal_entity_id;
5041:
5042:
5043: INSERT INTO per_za_employment_equity
5044: (
5045: report_id,
5046: reporting_date,
5047: business_group_id,

Line 5091: INSERT INTO per_za_employment_equity

5087:
5088: l_rowind := g_lev_Enc_Diff_table.first;
5089: loop
5090: exit when l_rowind is null;
5091: INSERT INTO per_za_employment_equity
5092: (
5093: report_id,
5094: reporting_date,
5095: business_group_id,

Line 5133: INSERT INTO per_za_employment_equity

5129: , g_lev_Enc_Diff_table(l_rowind).total
5130: FROM hr_all_organization_units haou
5131: Where haou.organization_id = g_lev_Enc_Diff_table(l_rowind).legal_entity_id;
5132:
5133: INSERT INTO per_za_employment_equity
5134: (
5135: report_id,
5136: reporting_date,
5137: business_group_id,

Line 5180: insert into per_za_employment_equity

5176: END loop;
5177: --hr_utility.trace_off;
5178:
5179: -- Inserts non-associated occupational categories with zero values for no of employees
5180: insert into per_za_employment_equity
5181: (
5182: report_id,
5183: reporting_date,
5184: business_group_id,

Line 5224: from per_za_employment_equity pzee

5220: , hr_all_organization_units haou
5221: where not exists
5222: (
5223: select 'X'
5224: from per_za_employment_equity pzee
5225: where pzee.level_cat_code = hl.lookup_code
5226: and pzee.report_id = 'ED1'
5227: and pzee.business_group_id = p_business_group_id --Bug 4872110
5228: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5235: insert into per_za_employment_equity

5231: and haou.business_group_id = p_business_group_id --Bug 4872110
5232: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5233:
5234: -- Inserts non-associated occupational categories with zero values for no of employees
5235: insert into per_za_employment_equity
5236: (
5237: report_id,
5238: reporting_date,
5239: business_group_id,

Line 5279: from per_za_employment_equity pzee

5275: , hr_all_organization_units haou
5276: where not exists
5277: (
5278: select 'X'
5279: from per_za_employment_equity pzee
5280: where pzee.level_cat_code = hl.lookup_code
5281: and pzee.report_id = 'ED1I'
5282: and pzee.business_group_id = p_business_group_id --Bug 4872110
5283: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5290: insert into per_za_employment_equity

5286: and haou.business_group_id = p_business_group_id --Bug 4872110
5287: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5288:
5289: -- inserting 0 values for the no of employees
5290: insert into per_za_employment_equity
5291: (
5292: report_id,
5293: reporting_date,
5294: business_group_id,

Line 5334: from per_za_employment_equity pzee

5330: , hr_all_organization_units haou
5331: where not exists
5332: (
5333: select 'X'
5334: from per_za_employment_equity pzee
5335: where pzee.level_cat_code = hl.lookup_code
5336: and pzee.report_id = 'ED2'
5337: and pzee.business_group_id = p_business_group_id --Bug 4872110
5338: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5345: insert into per_za_employment_equity

5341: and haou.business_group_id = p_business_group_id --Bug 4872110
5342: and haou.organization_id = nvl(p_legal_entity_id, haou.organization_id);
5343:
5344: -- inserting 0 values for the Income
5345: insert into per_za_employment_equity
5346: (
5347: report_id,
5348: reporting_date,
5349: business_group_id,

Line 5389: from per_za_employment_equity pzee

5385: , hr_all_organization_units haou
5386: where not exists
5387: (
5388: select 'X'
5389: from per_za_employment_equity pzee
5390: where pzee.level_cat_code = hl.lookup_code
5391: and pzee.report_id = 'ED2I'
5392: and pzee.business_group_id = p_business_group_id --Bug 4872110
5393: and pzee.legal_entity_id = nvl(p_legal_entity_id, haou.organization_id)

Line 5403: end per_za_employment_equity_pkg; -- package body

5399: commit;
5400:
5401: end init_g_cat_lev_table;
5402:
5403: end per_za_employment_equity_pkg; -- package body