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_PKG

Line 1: PACKAGE BODY PER_ZA_EMPLOYMENT_EQUITY_PKG as

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

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

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

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

711: select paaf.assignment_id,
712: paaf.person_id, -- Bug 4413678
713: paaf.payroll_id,
714: paei.aei_information7,
715: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
716: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
717: paaf.pay_basis_id
718: from per_assignment_extra_info paei,
719: per_assignment_status_types past,

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

712: paaf.person_id, -- Bug 4413678
713: paaf.payroll_id,
714: paei.aei_information7,
715: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
716: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
717: paaf.pay_basis_id
718: from per_assignment_extra_info paei,
719: per_assignment_status_types past,
720: per_all_assignments_f paaf

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

1847: paaf.business_group_id,
1848: paei.aei_information7 legal_entity_id,
1849: haou.name legal_entity,
1850: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1851: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1853: hl.lookup_code meaning_code,
1854: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1855: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

1848: paei.aei_information7 legal_entity_id,
1849: haou.name legal_entity,
1850: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1851: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1853: hl.lookup_code meaning_code,
1854: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1855: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1856: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

1850: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
1851: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
1852: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
1853: hl.lookup_code meaning_code,
1854: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
1855: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
1856: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
1857: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
1858: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

1887: and paei.aei_information7 is not null
1888: and nvl(paei.aei_information6, 'N') <> 'Y'
1889: and haou.organization_id = paei.aei_information7
1890: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
1891: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1892: AND hl.lookup_code <> '15' -- Not Applicable.
1893: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1894: AND hl1.lookup_code <> '15' -- Not Applicable.
1895: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

1891: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1892: AND hl.lookup_code <> '15' -- Not Applicable.
1893: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
1894: AND hl1.lookup_code <> '15' -- Not Applicable.
1895: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1896: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1897: AND hl2.lookup_code <> '15' -- Not Applicable.
1898: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1899: group by paaf.business_group_id,

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

1894: AND hl1.lookup_code <> '15' -- Not Applicable.
1895: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1896: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
1897: AND hl2.lookup_code <> '15' -- Not Applicable.
1898: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
1899: group by paaf.business_group_id,
1900: paei.aei_information7,
1901: haou.name,
1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073

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

1899: group by paaf.business_group_id,
1900: paei.aei_information7,
1901: haou.name,
1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1903: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1905: hl.lookup_code,
1906: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1907: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

1900: paei.aei_information7,
1901: haou.name,
1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1903: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1905: hl.lookup_code,
1906: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1907: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1908: decode(papf.PER_INFORMATION3,null,null,

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

1902: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
1903: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
1904: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
1905: hl.lookup_code,
1906: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
1907: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
1908: decode(papf.PER_INFORMATION3,null,null,
1909: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
1910: -to_char(p_report_date,'YYYYMMDD'))

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

2066: paaf.business_group_id,
2067: paei.aei_information7 legal_entity_id,
2068: haou.name legal_entity,
2069: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2070: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2072: hl.lookup_code meaning_code,
2073: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2074: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2067: paei.aei_information7 legal_entity_id,
2068: haou.name legal_entity,
2069: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2070: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2072: hl.lookup_code meaning_code,
2073: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2074: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2075: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

2069: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
2070: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2071: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
2072: hl.lookup_code meaning_code,
2073: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2074: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2075: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2076: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2077: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

2106: and paei.aei_information7 is not null
2107: and nvl(paei.aei_information6, 'N') <> 'Y'
2108: and haou.organization_id = paei.aei_information7
2109: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2110: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2111: AND hl.lookup_code <> '15' -- Not Applicable.
2112: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2113: AND hl1.lookup_code <> '15' -- Operation / core function
2114: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

2110: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2111: AND hl.lookup_code <> '15' -- Not Applicable.
2112: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2113: AND hl1.lookup_code <> '15' -- Operation / core function
2114: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2115: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2116: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2117: AND hl2.lookup_code <> '15' -- Not Applicable.
2118: group by paaf.business_group_id,

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

2112: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2113: AND hl1.lookup_code <> '15' -- Operation / core function
2114: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2115: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2116: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2117: AND hl2.lookup_code <> '15' -- Not Applicable.
2118: group by paaf.business_group_id,
2119: paei.aei_information7,
2120: haou.name,

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

2118: group by paaf.business_group_id,
2119: paei.aei_information7,
2120: haou.name,
2121: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2122: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2124: hl.lookup_code,
2125: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2126: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2119: paei.aei_information7,
2120: haou.name,
2121: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2122: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2124: hl.lookup_code,
2125: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2126: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2127: decode(papf.PER_INFORMATION3,null,null,

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

2121: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2122: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
2123: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
2124: hl.lookup_code,
2125: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2126: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2127: decode(papf.PER_INFORMATION3,null,null,
2128: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2129: -to_char(p_report_date,'YYYYMMDD'))

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

2303: paaf.business_group_id,
2304: paei.aei_information7 legal_entity_id,
2305: haou.name legal_entity,
2306: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2307: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2309: hl.lookup_code meaning_code,
2310: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2311: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2304: paei.aei_information7 legal_entity_id,
2305: haou.name legal_entity,
2306: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2307: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2309: hl.lookup_code meaning_code,
2310: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2311: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2312: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

2306: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2307: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2308: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2309: hl.lookup_code meaning_code,
2310: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2311: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2312: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2313: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2314: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

2343: and paei.aei_information7 is not null
2344: and nvl(paei.aei_information6, 'N') <> 'Y'
2345: and haou.organization_id = paei.aei_information7
2346: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2347: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2348: AND hl.lookup_code <> '15' -- Not Applicable.
2349: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2350: AND hl1.lookup_code = '1' -- Operation / core function
2351: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

2347: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2348: AND hl.lookup_code <> '15' -- Not Applicable.
2349: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2350: AND hl1.lookup_code = '1' -- Operation / core function
2351: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2352: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2353: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2354: AND hl2.lookup_code <> '15' -- Not Applicable.
2355: group by paaf.business_group_id,

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

2349: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2350: AND hl1.lookup_code = '1' -- Operation / core function
2351: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2352: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2353: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2354: AND hl2.lookup_code <> '15' -- Not Applicable.
2355: group by paaf.business_group_id,
2356: paei.aei_information7,
2357: haou.name,

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

2355: group by paaf.business_group_id,
2356: paei.aei_information7,
2357: haou.name,
2358: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2359: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2361: hl.lookup_code,
2362: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2363: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2356: paei.aei_information7,
2357: haou.name,
2358: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2359: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2361: hl.lookup_code,
2362: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2363: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2364: decode(papf.PER_INFORMATION3,null,null,

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

2358: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2359: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2360: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2361: hl.lookup_code,
2362: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2363: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2364: decode(papf.PER_INFORMATION3,null,null,
2365: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2366: -to_char(p_report_date,'YYYYMMDD'))

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

2550: paaf.business_group_id,
2551: paei.aei_information7 legal_entity_id,
2552: haou.name legal_entity,
2553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2556: hl.lookup_code meaning_code,
2557: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2558: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2551: paei.aei_information7 legal_entity_id,
2552: haou.name legal_entity,
2553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2556: hl.lookup_code meaning_code,
2557: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2558: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

2553: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2554: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2555: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2556: hl.lookup_code meaning_code,
2557: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2558: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2559: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2560: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2561: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

2590: and paei.aei_information7 is not null
2591: and nvl(paei.aei_information6, 'N') <> 'Y'
2592: and haou.organization_id = paei.aei_information7
2593: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2594: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2595: AND hl.lookup_code <> '15' -- Not Applicable.
2596: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2597: AND hl1.lookup_code = '2' -- Support function
2598: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

2594: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2595: AND hl.lookup_code <> '15' -- Not Applicable.
2596: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2597: AND hl1.lookup_code = '2' -- Support function
2598: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2599: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2600: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2601: AND hl2.lookup_code <> '15' -- Not Applicable.
2602: group by paaf.business_group_id,

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

2596: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2597: AND hl1.lookup_code = '2' -- Support function
2598: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2599: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2600: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2601: AND hl2.lookup_code <> '15' -- Not Applicable.
2602: group by paaf.business_group_id,
2603: paei.aei_information7,
2604: haou.name,

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

2602: group by paaf.business_group_id,
2603: paei.aei_information7,
2604: haou.name,
2605: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2606: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2608: hl.lookup_code,
2609: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2610: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2603: paei.aei_information7,
2604: haou.name,
2605: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2606: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2608: hl.lookup_code,
2609: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2610: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2611: decode(papf.PER_INFORMATION3,null,null,

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

2605: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2606: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2607: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2608: hl.lookup_code,
2609: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2610: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2611: decode(papf.PER_INFORMATION3,null,null,
2612: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2613: -to_char(p_report_date,'YYYYMMDD'))

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

2800: paaf.business_group_id,
2801: paei.aei_information7 legal_entity_id,
2802: haou.name legal_entity,
2803: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2804: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2806: hl.lookup_code meaning_code,
2807: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2808: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

2801: paei.aei_information7 legal_entity_id,
2802: haou.name legal_entity,
2803: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2804: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2806: hl.lookup_code meaning_code,
2807: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2808: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2809: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

2803: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
2804: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
2805: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
2806: hl.lookup_code meaning_code,
2807: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
2808: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
2809: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
2810: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
2811: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

2843: and paei.aei_information7 is not null
2844: and nvl(paei.aei_information6, 'N') <> 'Y'
2845: and haou.organization_id = paei.aei_information7
2846: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
2847: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2848: AND hl.lookup_code <> '15' -- Not Applicable.
2849: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2850: and hl1.lookup_code <> '15' -- Not Applicable.
2851: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

2847: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2848: AND hl.lookup_code <> '15' -- Not Applicable.
2849: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2850: and hl1.lookup_code <> '15' -- Not Applicable.
2851: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2852: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2853: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2854: AND hl2.lookup_code <> '15' -- Not Applicable.
2855: group by paaf.business_group_id,

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

2849: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
2850: and hl1.lookup_code <> '15' -- Not Applicable.
2851: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2852: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
2853: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
2854: AND hl2.lookup_code <> '15' -- Not Applicable.
2855: group by paaf.business_group_id,
2856: paei.aei_information7,
2857: haou.name,

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

2855: group by paaf.business_group_id,
2856: paei.aei_information7,
2857: haou.name,
2858: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2859: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2861: hl.lookup_code,
2862: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2863: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

2856: paei.aei_information7,
2857: haou.name,
2858: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2859: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2861: hl.lookup_code,
2862: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2863: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2864: decode(papf.PER_INFORMATION3,null,null,

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

2858: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
2859: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
2860: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
2861: hl.lookup_code,
2862: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
2863: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
2864: decode(papf.PER_INFORMATION3,null,null,
2865: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
2866: -to_char(p_report_date,'YYYYMMDD'))

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

3030: paaf.business_group_id,
3031: paei.aei_information7 legal_entity_id,
3032: haou.name legal_entity,
3033: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3034: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
3036: hl.lookup_code lookup_code,
3037: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3038: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

3031: paei.aei_information7 legal_entity_id,
3032: haou.name legal_entity,
3033: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3034: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
3036: hl.lookup_code lookup_code,
3037: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3038: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3039: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

3033: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3034: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3035: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
3036: hl.lookup_code lookup_code,
3037: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3038: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3039: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3040: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3041: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

3076: and paei.aei_information7 is not null
3077: and nvl(paei.aei_information6, 'N') <> 'Y'
3078: and haou.organization_id = paei.aei_information7
3079: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3080: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3081: AND hl.lookup_code <> '15' -- Not Applicable.
3082: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3083: and hl1.lookup_code <> '15' -- Not Applicable.
3084: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

3080: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3081: AND hl.lookup_code <> '15' -- Not Applicable.
3082: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3083: and hl1.lookup_code <> '15' -- Not Applicable.
3084: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3085: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3086: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <

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

3082: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3083: and hl1.lookup_code <> '15' -- Not Applicable.
3084: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3085: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3086: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3089: any
3090: (

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

3084: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3085: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3086: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3089: any
3090: (
3091: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3092: from per_all_assignments_f paaf1

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

3087: AND hl2.lookup_code <> '15' -- Not Applicable.
3088: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)), '9999999999') <
3089: any
3090: (
3091: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3092: from per_all_assignments_f paaf1
3093: where paaf1.person_id = papf.person_id
3094: and paaf1.primary_flag = 'Y'
3095: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null

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

3091: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) lookup_code
3092: from per_all_assignments_f paaf1
3093: where paaf1.person_id = papf.person_id
3094: and paaf1.primary_flag = 'Y'
3095: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id)) is not null
3096: and paaf1.effective_end_date + 1 = paaf.effective_start_date
3097: )
3098: group by paaf.business_group_id,
3099: paei.aei_information7,

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

3098: group by paaf.business_group_id,
3099: paei.aei_information7,
3100: haou.name,
3101: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3102: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3104: hl.lookup_code,
3105: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3106: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

3099: paei.aei_information7,
3100: haou.name,
3101: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3102: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3104: hl.lookup_code,
3105: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3106: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3107: decode(papf.PER_INFORMATION3,null,null,

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

3101: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3102: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
3103: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
3104: hl.lookup_code,
3105: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3106: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3107: decode(papf.PER_INFORMATION3,null,null,
3108: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3109: -to_char(p_report_date,'YYYYMMDD'))

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

3285: paaf.business_group_id,
3286: paei.aei_information7 legal_entity_id,
3287: haou.name legal_entity,
3288: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3289: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3291: hl.lookup_code meaning_code,
3292: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3293: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

3286: paei.aei_information7 legal_entity_id,
3287: haou.name legal_entity,
3288: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3289: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3291: hl.lookup_code meaning_code,
3292: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3293: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3294: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

3288: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
3289: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
3290: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
3291: hl.lookup_code meaning_code,
3292: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level') occupational_level,
3293: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
3294: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
3295: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
3296: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

3328: and paei.aei_information7 is not null
3329: and nvl(paei.aei_information6, 'N') <> 'Y'
3330: and haou.organization_id = paei.aei_information7
3331: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3332: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3333: AND hl.lookup_code <> '15' -- Not Applicable.
3334: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3335: and hl1.lookup_code <> '15' -- Not Applicable
3336: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

3332: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3333: AND hl.lookup_code <> '15' -- Not Applicable.
3334: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3335: and hl1.lookup_code <> '15' -- Not Applicable
3336: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3337: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3338: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3339: AND hl2.lookup_code <> '15' -- Not Applicable.
3340: group by paaf.business_group_id,

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

3334: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3335: and hl1.lookup_code <> '15' -- Not Applicable
3336: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3337: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3338: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3339: AND hl2.lookup_code <> '15' -- Not Applicable.
3340: group by paaf.business_group_id,
3341: paei.aei_information7,
3342: haou.name,

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

3340: group by paaf.business_group_id,
3341: paei.aei_information7,
3342: haou.name,
3343: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3344: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3346: hl.lookup_code,
3347: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3348: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

3341: paei.aei_information7,
3342: haou.name,
3343: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3344: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3346: hl.lookup_code,
3347: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3348: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3349: decode(papf.PER_INFORMATION3,null,null,

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

3343: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
3344: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
3345: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
3346: hl.lookup_code,
3347: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Level'),
3348: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
3349: decode(papf.PER_INFORMATION3,null,null,
3350: decode(sign(replace(nvl(substr(papf.PER_INFORMATION8,1,10),'0001/01/01'),'/','')
3351: -to_char(p_report_date,'YYYYMMDD'))

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

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

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

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

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

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

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

3593: and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
3594: and paei.aei_information7 is not null
3595: and nvl(paei.aei_information6, 'N') <> 'Y'
3596: and haou.organization_id = paei.aei_information7
3597: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3598: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3599: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3600: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3601: and hl.lookup_code <> '15' -- Not Applicable

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

3594: and paei.aei_information7 is not null
3595: and nvl(paei.aei_information6, 'N') <> 'Y'
3596: and haou.organization_id = paei.aei_information7
3597: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3598: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3599: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3600: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3601: and hl.lookup_code <> '15' -- Not Applicable
3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'

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

3596: and haou.organization_id = paei.aei_information7
3597: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
3598: and nvl(per_za_employment_equity_pkg.get_termination_reason(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
3599: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
3600: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3601: and hl.lookup_code <> '15' -- Not Applicable
3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3603: and hl1.lookup_code <> '15' -- Not Applicable
3604: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

3600: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3601: and hl.lookup_code <> '15' -- Not Applicable
3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3603: and hl1.lookup_code <> '15' -- Not Applicable
3604: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3605: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3606: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3607: and hl2.lookup_code <> '15' -- Not Applicable
3608: group by paaf.business_group_id,

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

3602: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
3603: and hl1.lookup_code <> '15' -- Not Applicable
3604: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3605: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
3606: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
3607: and hl2.lookup_code <> '15' -- Not Applicable
3608: group by paaf.business_group_id,
3609: paei.aei_information7,
3610: haou.name,

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

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

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

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

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

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

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

4582: paaf.payroll_id,
4583: paei.aei_information7 ,
4584: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4585: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4586: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4587: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4588: paaf.pay_basis_id
4589: from per_assignment_extra_info paei,
4590: per_assignment_status_types past,

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

4583: paei.aei_information7 ,
4584: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4585: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4586: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4587: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4588: paaf.pay_basis_id
4589: from per_assignment_extra_info paei,
4590: per_assignment_status_types past,
4591: per_all_assignments_f paaf,

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

4608: AND hl_fn.lookup_code <>'15'
4609: AND hl_cat.application_id = '800'
4610: AND hl_lev.application_id = '800'
4611: AND hl_fn.application_id = '800'
4612: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4613: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4614: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4615: and nvl(paei.aei_information6, 'N') <> 'Y'
4616: order BY paei.aei_information7, paaf.payroll_id;

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

4609: AND hl_cat.application_id = '800'
4610: AND hl_lev.application_id = '800'
4611: AND hl_fn.application_id = '800'
4612: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4613: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4614: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4615: and nvl(paei.aei_information6, 'N') <> 'Y'
4616: order BY paei.aei_information7, paaf.payroll_id;
4617: */

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

4610: AND hl_lev.application_id = '800'
4611: AND hl_fn.application_id = '800'
4612: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4613: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4614: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4615: and nvl(paei.aei_information6, 'N') <> 'Y'
4616: order BY paei.aei_information7, paaf.payroll_id;
4617: */
4618:

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

4622: paaf.payroll_id,
4623: paei.aei_information7 ,
4624: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4625: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4626: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4627: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4628: paaf.pay_basis_id
4629: from per_assignment_extra_info paei,
4630: per_all_assignments_f paaf,

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

4623: paei.aei_information7 ,
4624: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
4625: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
4626: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
4627: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
4628: paaf.pay_basis_id
4629: from per_assignment_extra_info paei,
4630: per_all_assignments_f paaf,
4631: hr_lookups hl_cat,

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

4653: AND hl_fn.lookup_code <>'15'
4654: AND hl_cat.application_id = '800'
4655: AND hl_lev.application_id = '800'
4656: AND hl_fn.application_id = '800'
4657: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4658: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4659: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;

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

4654: AND hl_cat.application_id = '800'
4655: AND hl_lev.application_id = '800'
4656: AND hl_fn.application_id = '800'
4657: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4658: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4659: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;
4662:

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

4655: AND hl_lev.application_id = '800'
4656: AND hl_fn.application_id = '800'
4657: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4658: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4659: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
4660: and nvl(paei.aei_information6, 'N') <> 'Y'
4661: order BY paei.aei_information7, paaf.payroll_id;
4662:
4663:

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

5979: paaf.payroll_id,
5980: paei.aei_information7 ,
5981: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
5982: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
5983: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
5984: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
5985: paaf.pay_basis_id
5986: from per_assignment_extra_info paei,
5987: per_assignment_status_types past,

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

5980: paei.aei_information7 ,
5981: hl_cat.lookup_code OCCUPATIONAL_CATEGORY_ID,
5982: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
5983: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_level,
5984: per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id) occupational_category,
5985: paaf.pay_basis_id
5986: from per_assignment_extra_info paei,
5987: per_assignment_status_types past,
5988: per_all_assignments_f paaf,

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

6005: AND hl_fn.lookup_code <>'15'
6006: AND hl_cat.application_id = '800'
6007: AND hl_lev.application_id = '800'
6008: AND hl_fn.application_id = '800'
6009: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6010: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6011: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6012: and nvl(paei.aei_information6, 'N') <> 'Y'
6013: order BY paei.aei_information7, paaf.payroll_id;

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

6006: AND hl_cat.application_id = '800'
6007: AND hl_lev.application_id = '800'
6008: AND hl_fn.application_id = '800'
6009: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6010: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6011: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6012: and nvl(paei.aei_information6, 'N') <> 'Y'
6013: order BY paei.aei_information7, paaf.payroll_id;
6014: */

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

6007: AND hl_lev.application_id = '800'
6008: AND hl_fn.application_id = '800'
6009: AND hl_cat.meaning(+) = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6010: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6011: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
6012: and nvl(paei.aei_information6, 'N') <> 'Y'
6013: order BY paei.aei_information7, paaf.payroll_id;
6014: */
6015:

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

6019: paaf.person_id, -- Bug 4413678
6020: paaf.payroll_id,
6021: paei.aei_information7 ,
6022: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
6023: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009) occupational_level,
6024: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type,
6025: paaf.pay_basis_id
6026: from per_assignment_extra_info paei,
6027: per_all_assignments_f paaf,

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

6020: paaf.payroll_id,
6021: paei.aei_information7 ,
6022: hl_lev.lookup_code OCCUPATIONAL_LEVEL_ID,
6023: per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009) occupational_level,
6024: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type,
6025: paaf.pay_basis_id
6026: from per_assignment_extra_info paei,
6027: per_all_assignments_f paaf,
6028: hr_lookups hl_lev,

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

6046: AND hl_lev.lookup_code <> '15'
6047: AND hl_fn.lookup_code <>'15'
6048: AND hl_lev.application_id = '800'
6049: AND hl_fn.application_id = '800'
6050: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
6051: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
6052: and nvl(paei.aei_information6, 'N') <> 'Y'
6053: order BY paei.aei_information7, paaf.payroll_id;
6054:

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

6047: AND hl_fn.lookup_code <>'15'
6048: AND hl_lev.application_id = '800'
6049: AND hl_fn.application_id = '800'
6050: AND hl_lev.meaning(+) = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
6051: AND hl_fn.meaning(+) = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
6052: and nvl(paei.aei_information6, 'N') <> 'Y'
6053: order BY paei.aei_information7, paaf.payroll_id;
6054:
6055:

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

7434: paaf.business_group_id,
7435: paei.aei_information7 legal_entity_id,
7436: haou.name legal_entity,
7437: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7438: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7440: hl.lookup_code meaning_code,
7441: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
7442: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

7435: paei.aei_information7 legal_entity_id,
7436: haou.name legal_entity,
7437: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7438: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7440: hl.lookup_code meaning_code,
7441: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
7442: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7443: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

7437: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7438: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7439: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7440: hl.lookup_code meaning_code,
7441: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category') occupational_category,
7442: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7443: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
7444: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
7445: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

7474: and paei.aei_information7 is not null
7475: and nvl(paei.aei_information6, 'N') <> 'Y'
7476: and haou.organization_id = paei.aei_information7
7477: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7478: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7479: AND hl.lookup_code <> '15' -- Not Applicable.
7480: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7481: AND hl1.lookup_code <> '15' -- Not Applicable.
7482: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)

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

7478: and hl.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7479: AND hl.lookup_code <> '15' -- Not Applicable.
7480: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7481: AND hl1.lookup_code <> '15' -- Not Applicable.
7482: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7483: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7484: AND hl2.lookup_code <> '15' -- Not Applicable.
7485: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7486: group by paaf.business_group_id,

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

7481: AND hl1.lookup_code <> '15' -- Not Applicable.
7482: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7483: and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7484: AND hl2.lookup_code <> '15' -- Not Applicable.
7485: and hl2.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7486: group by paaf.business_group_id,
7487: paei.aei_information7,
7488: haou.name,
7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073

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

7486: group by paaf.business_group_id,
7487: paei.aei_information7,
7488: haou.name,
7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7490: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7492: hl.lookup_code,
7493: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
7494: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

7487: paei.aei_information7,
7488: haou.name,
7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7490: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7492: hl.lookup_code,
7493: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
7494: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7495: decode(papf.PER_INFORMATION11,null,null,

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

7489: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7490: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
7491: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7492: hl.lookup_code,
7493: nvl(per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id), 'No Occupational Category'),
7494: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7495: decode(papf.PER_INFORMATION11,null,null,
7496: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
7497: -to_char(l_nat_date,'YYYYMMDD'))

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

7653: paaf.business_group_id,
7654: paei.aei_information7 legal_entity_id,
7655: haou.name legal_entity,
7656: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7657: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7659: hl.lookup_code meaning_code,
7660: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
7661: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

7654: paei.aei_information7 legal_entity_id,
7655: haou.name legal_entity,
7656: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7657: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7659: hl.lookup_code meaning_code,
7660: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
7661: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7662: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

7656: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
7657: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7658: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
7659: hl.lookup_code meaning_code,
7660: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
7661: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7662: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
7663: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
7664: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

7693: and paei.aei_information7 is not null
7694: and nvl(paei.aei_information6, 'N') <> 'Y'
7695: and haou.organization_id = paei.aei_information7
7696: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7697: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7698: AND hl.lookup_code <> '15' -- Not Applicable.
7699: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7700: AND hl1.lookup_code <> '15' -- Operation / core function
7701: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

7697: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7698: AND hl.lookup_code <> '15' -- Not Applicable.
7699: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7700: AND hl1.lookup_code <> '15' -- Operation / core function
7701: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7702: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7703: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7704: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7705: group by paaf.business_group_id,

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

7699: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7700: AND hl1.lookup_code <> '15' -- Operation / core function
7701: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7702: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7703: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7704: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7705: group by paaf.business_group_id,
7706: paei.aei_information7,
7707: haou.name,

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

7705: group by paaf.business_group_id,
7706: paei.aei_information7,
7707: haou.name,
7708: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7709: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7711: hl.lookup_code,
7712: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
7713: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

7706: paei.aei_information7,
7707: haou.name,
7708: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7709: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7711: hl.lookup_code,
7712: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
7713: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7714: decode(papf.PER_INFORMATION11,null,null,

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

7708: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7709: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)), -- Bug 3962073
7710: -- per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id), -- Bug 3962073
7711: hl.lookup_code,
7712: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
7713: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7714: decode(papf.PER_INFORMATION11,null,null,
7715: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
7716: -to_char(l_nat_date,'YYYYMMDD'))

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

7890: paaf.business_group_id,
7891: paei.aei_information7 legal_entity_id,
7892: haou.name legal_entity,
7893: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
7894: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
7896: hl.lookup_code meaning_code,
7897: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
7898: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

7891: paei.aei_information7 legal_entity_id,
7892: haou.name legal_entity,
7893: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
7894: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
7896: hl.lookup_code meaning_code,
7897: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
7898: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7899: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

7893: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
7894: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
7895: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
7896: hl.lookup_code meaning_code,
7897: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
7898: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
7899: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
7900: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
7901: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

7930: and paei.aei_information7 is not null
7931: and nvl(paei.aei_information6, 'N') <> 'Y'
7932: and haou.organization_id = paei.aei_information7
7933: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
7934: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7935: AND hl.lookup_code <> '15' -- Not Applicable.
7936: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7937: AND hl1.lookup_code = '1' -- Operation / core function
7938: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

7934: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7935: AND hl.lookup_code <> '15' -- Not Applicable.
7936: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7937: AND hl1.lookup_code = '1' -- Operation / core function
7938: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7939: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7940: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7941: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7942: group by paaf.business_group_id,

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

7936: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
7937: AND hl1.lookup_code = '1' -- Operation / core function
7938: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
7939: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
7940: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
7941: -- AND hl2.lookup_code <> '15' -- Not Applicable.
7942: group by paaf.business_group_id,
7943: paei.aei_information7,
7944: haou.name,

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

7942: group by paaf.business_group_id,
7943: paei.aei_information7,
7944: haou.name,
7945: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7946: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
7948: hl.lookup_code,
7949: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
7950: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

7943: paei.aei_information7,
7944: haou.name,
7945: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7946: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
7948: hl.lookup_code,
7949: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
7950: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7951: decode(papf.PER_INFORMATION11,null,null,

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

7945: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
7946: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
7947: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
7948: hl.lookup_code,
7949: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
7950: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
7951: decode(papf.PER_INFORMATION11,null,null,
7952: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
7953: -to_char(l_nat_date,'YYYYMMDD'))

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

8137: paaf.business_group_id,
8138: paei.aei_information7 legal_entity_id,
8139: haou.name legal_entity,
8140: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8141: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8143: hl.lookup_code meaning_code,
8144: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8145: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

8138: paei.aei_information7 legal_entity_id,
8139: haou.name legal_entity,
8140: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8141: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8143: hl.lookup_code meaning_code,
8144: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8145: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8146: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

8140: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8141: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8142: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8143: hl.lookup_code meaning_code,
8144: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8145: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8146: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8147: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8148: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

8177: and paei.aei_information7 is not null
8178: and nvl(paei.aei_information6, 'N') <> 'Y'
8179: and haou.organization_id = paei.aei_information7
8180: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8181: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8182: AND hl.lookup_code <> '15' -- Not Applicable.
8183: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8184: AND hl1.lookup_code = '2' -- Support function
8185: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

8181: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8182: AND hl.lookup_code <> '15' -- Not Applicable.
8183: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8184: AND hl1.lookup_code = '2' -- Support function
8185: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8186: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8187: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8188: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8189: group by paaf.business_group_id,

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

8183: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8184: AND hl1.lookup_code = '2' -- Support function
8185: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8186: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8187: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8188: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8189: group by paaf.business_group_id,
8190: paei.aei_information7,
8191: haou.name,

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

8189: group by paaf.business_group_id,
8190: paei.aei_information7,
8191: haou.name,
8192: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8193: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8195: hl.lookup_code,
8196: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8197: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

8190: paei.aei_information7,
8191: haou.name,
8192: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8193: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8195: hl.lookup_code,
8196: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8197: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8198: decode(papf.PER_INFORMATION11,null,null,

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

8192: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8193: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
8194: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8195: hl.lookup_code,
8196: nvl(per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8197: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8198: decode(papf.PER_INFORMATION11,null,null,
8199: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8200: -to_char(l_nat_date,'YYYYMMDD'))

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

8387: paaf.business_group_id,
8388: paei.aei_information7 legal_entity_id,
8389: haou.name legal_entity,
8390: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8391: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8393: hl.lookup_code meaning_code,
8394: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8395: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

8388: paei.aei_information7 legal_entity_id,
8389: haou.name legal_entity,
8390: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8391: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8393: hl.lookup_code meaning_code,
8394: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8395: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8396: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

8390: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, -- 3962073
8391: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8392: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id) employment_type, -- Bug 3962073
8393: hl.lookup_code meaning_code,
8394: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8395: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8396: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8397: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8398: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

8430: and paei.aei_information7 is not null
8431: and nvl(paei.aei_information6, 'N') <> 'Y'
8432: and haou.organization_id = paei.aei_information7
8433: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8434: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8435: AND hl.lookup_code <> '15' -- Not Applicable.
8436: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8437: and hl1.lookup_code <> '15' -- Not Applicable.
8438: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

8434: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8435: AND hl.lookup_code <> '15' -- Not Applicable.
8436: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8437: and hl1.lookup_code <> '15' -- Not Applicable.
8438: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8439: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8440: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8441: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8442: group by paaf.business_group_id,

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

8436: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8437: and hl1.lookup_code <> '15' -- Not Applicable.
8438: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8439: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8440: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8441: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8442: group by paaf.business_group_id,
8443: paei.aei_information7,
8444: haou.name,

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

8442: group by paaf.business_group_id,
8443: paei.aei_information7,
8444: haou.name,
8445: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8446: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8448: hl.lookup_code,
8449: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8450: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

8443: paei.aei_information7,
8444: haou.name,
8445: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8446: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8448: hl.lookup_code,
8449: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8450: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8451: decode(papf.PER_INFORMATION11,null,null,

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

8445: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8446: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(p_report_date, paaf.period_of_service_id)),
8447: -- per_za_employment_equity_pkg.get_ee_employment_type_name(ppos.date_start, paaf.period_of_service_id),
8448: hl.lookup_code,
8449: nvl(per_za_employment_equity_pkg.get_occupational_level(ppos.date_start, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8450: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8451: decode(papf.PER_INFORMATION11,null,null,
8452: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8453: -to_char(l_nat_date,'YYYYMMDD'))

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

8617: paaf.business_group_id,
8618: paei.aei_information7 legal_entity_id,
8619: haou.name legal_entity,
8620: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8621: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
8623: hl.lookup_code lookup_code,
8624: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8625: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

8618: paei.aei_information7 legal_entity_id,
8619: haou.name legal_entity,
8620: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8621: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
8623: hl.lookup_code lookup_code,
8624: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8625: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8626: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

8620: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8621: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8622: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id) employment_type,
8623: hl.lookup_code lookup_code,
8624: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8625: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8626: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8627: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8628: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

8663: and paei.aei_information7 is not null
8664: and nvl(paei.aei_information6, 'N') <> 'Y'
8665: and haou.organization_id = paei.aei_information7
8666: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8667: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8668: AND hl.lookup_code <> '15' -- Not Applicable.
8669: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8670: and hl1.lookup_code <> '15' -- Not Applicable.
8671: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

8667: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8668: AND hl.lookup_code <> '15' -- Not Applicable.
8669: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8670: and hl1.lookup_code <> '15' -- Not Applicable.
8671: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8672: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8673: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)), '9999999999') <

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

8669: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8670: and hl1.lookup_code <> '15' -- Not Applicable.
8671: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8672: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8673: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)), '9999999999') <
8676: any
8677: (

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

8671: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8672: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8673: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)), '9999999999') <
8676: any
8677: (
8678: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) lookup_code
8679: from per_all_assignments_f paaf1

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

8674: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8675: and nvl(per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)), '9999999999') <
8676: any
8677: (
8678: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) lookup_code
8679: from per_all_assignments_f paaf1
8680: where paaf1.person_id = papf.person_id
8681: and paaf1.primary_flag = 'Y'
8682: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) is not null

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

8678: select per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) lookup_code
8679: from per_all_assignments_f paaf1
8680: where paaf1.person_id = papf.person_id
8681: and paaf1.primary_flag = 'Y'
8682: and per_za_employment_equity_pkg.get_lookup_code(per_za_employment_equity_pkg.get_occupational_level(paaf1.effective_start_date, paaf1.assignment_id, paaf1.job_id, paaf1.grade_id, paaf1.position_id, paaf.business_group_id,2009)) is not null
8683: and paaf1.effective_end_date + 1 = paaf.effective_start_date
8684: )
8685: group by paaf.business_group_id,
8686: paei.aei_information7,

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

8685: group by paaf.business_group_id,
8686: paei.aei_information7,
8687: haou.name,
8688: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8689: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
8691: hl.lookup_code,
8692: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8693: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

8686: paei.aei_information7,
8687: haou.name,
8688: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8689: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
8691: hl.lookup_code,
8692: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8693: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8694: decode(papf.PER_INFORMATION11,null,null,

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

8688: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8689: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id)),
8690: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_start_date, paaf.period_of_service_id),
8691: hl.lookup_code,
8692: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_start_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8693: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8694: decode(papf.PER_INFORMATION11,null,null,
8695: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8696: -to_char(l_nat_date,'YYYYMMDD'))

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

8872: paaf.business_group_id,
8873: paei.aei_information7 legal_entity_id,
8874: haou.name legal_entity,
8875: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8876: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
8878: hl.lookup_code meaning_code,
8879: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8880: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

8873: paei.aei_information7 legal_entity_id,
8874: haou.name legal_entity,
8875: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8876: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
8878: hl.lookup_code meaning_code,
8879: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8880: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8881: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

8875: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
8876: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
8877: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
8878: hl.lookup_code meaning_code,
8879: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level') occupational_level,
8880: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
8881: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
8882: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
8883: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

8915: and paei.aei_information7 is not null
8916: and nvl(paei.aei_information6, 'N') <> 'Y'
8917: and haou.organization_id = paei.aei_information7
8918: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
8919: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8920: AND hl.lookup_code <> '15' -- Not Applicable.
8921: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8922: and hl1.lookup_code <> '15' -- Not Applicable
8923: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

8919: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8920: AND hl.lookup_code <> '15' -- Not Applicable.
8921: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8922: and hl1.lookup_code <> '15' -- Not Applicable
8923: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8924: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8925: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8926: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8927: group by paaf.business_group_id,

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

8921: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
8922: and hl1.lookup_code <> '15' -- Not Applicable
8923: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
8924: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
8925: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
8926: -- AND hl2.lookup_code <> '15' -- Not Applicable.
8927: group by paaf.business_group_id,
8928: paei.aei_information7,
8929: haou.name,

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

8927: group by paaf.business_group_id,
8928: paei.aei_information7,
8929: haou.name,
8930: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8931: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
8933: hl.lookup_code,
8934: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8935: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,

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

8928: paei.aei_information7,
8929: haou.name,
8930: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8931: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
8933: hl.lookup_code,
8934: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8935: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8936: decode(papf.PER_INFORMATION11,null,null,

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

8930: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag), --3962073
8931: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)),
8932: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id),
8933: hl.lookup_code,
8934: nvl(per_za_employment_equity_pkg.get_occupational_level(paaf.effective_end_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009), 'No Occupational Level'),
8935: p_report_code || decode(papf.PER_INFORMATION9,'N',null,'Y','F',null,
8936: decode(papf.PER_INFORMATION11,null,null,
8937: decode(sign(replace(nvl(substr(papf.PER_INFORMATION11,1,10),'0001/01/01'),'/','')
8938: -to_char(l_nat_date,'YYYYMMDD'))

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

9141: paaf.business_group_id,
9142: paei.aei_information7 legal_entity_id,
9143: haou.name legal_entity,
9144: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
9145: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
9147: ppos.leaving_reason meaning_code,
9148: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
9149: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,

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

9142: paei.aei_information7 legal_entity_id,
9143: haou.name legal_entity,
9144: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
9145: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
9147: ppos.leaving_reason meaning_code,
9148: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
9149: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
9150: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,

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

9144: decode(papf.registered_disabled_flag,'F','Y','P','Y',papf.registered_disabled_flag) disability, --3962073
9145: nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) employment_type, -- Bug 3962073
9146: -- per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id) employment_type, -- Bug 3962073
9147: ppos.leaving_reason meaning_code,
9148: nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') termination_reason,
9149: sum(decode(papf.sex, 'M', decode(papf.per_information4, '02', 1,'ZA01', 1, 0), 0)) male_african,
9150: sum(decode(papf.sex, 'M', decode(papf.per_information4, '03', 1, 0), 0)) male_coloured,
9151: sum(decode(papf.sex, 'M', decode(papf.per_information4, '01', 1, 0), 0)) male_indian,
9152: sum(decode(papf.sex, 'M', decode(papf.per_information4, '04', 1, 0), 0)) male_white,

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

9183: and paei.aei_information7 = nvl(p_legal_entity_id, paei.aei_information7)
9184: and paei.aei_information7 is not null
9185: and nvl(paei.aei_information6, 'N') <> 'Y'
9186: and haou.organization_id = paei.aei_information7
9187: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
9188: and nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
9189: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9190: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
9191: and hl.lookup_code <> '15' -- Not Applicable

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

9184: and paei.aei_information7 is not null
9185: and nvl(paei.aei_information6, 'N') <> 'Y'
9186: and haou.organization_id = paei.aei_information7
9187: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
9188: and nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
9189: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9190: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
9191: and hl.lookup_code <> '15' -- Not Applicable
9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'

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

9186: and haou.organization_id = paei.aei_information7
9187: and nvl(decode(paei.aei_information11,'P','Permanent','N','Non-Permanent'), per_za_employment_equity_pkg.get_ee_employment_type_name(paaf.effective_end_date, paaf.period_of_service_id)) = 'Permanent'
9188: and nvl(per_za_employment_equity_pkg.get_termination_reason_new(paaf.business_group_id, p_report_date, ppos.leaving_reason), 'No Leaving Reason') <> 'No Leaving Reason'
9189: and hl.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_LEV'
9190: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
9191: and hl.lookup_code <> '15' -- Not Applicable
9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
9193: and hl1.lookup_code <> '15' -- Not Applicable
9194: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)

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

9190: and hl.meaning = per_za_employment_equity_pkg.get_occupational_level(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
9191: and hl.lookup_code <> '15' -- Not Applicable
9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
9193: and hl1.lookup_code <> '15' -- Not Applicable
9194: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
9195: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
9196: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
9197: -- and hl2.lookup_code <> '15' -- Not Applicable
9198: group by paaf.business_group_id,

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

9192: and hl1.lookup_type = 'ZA_EE_FUNCTION_TYPE'
9193: and hl1.lookup_code <> '15' -- Not Applicable
9194: and hl1.meaning = per_za_employment_equity_pkg.get_functional_type(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id,2009)
9195: -- and hl2.lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
9196: -- and hl2.meaning = per_za_employment_equity_pkg.get_occupational_category(p_report_date, paaf.assignment_id, paaf.job_id, paaf.grade_id, paaf.position_id, paaf.business_group_id)
9197: -- and hl2.lookup_code <> '15' -- Not Applicable
9198: group by paaf.business_group_id,
9199: paei.aei_information7,
9200: haou.name,

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

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

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

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

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

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

Line 9591: end per_za_employment_equity_pkg; -- package body

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