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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 5403: end per_za_employment_equity_pkg; -- package body

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