1 package per_za_employment_equity_pkg as
2 /* $Header: perzaeer.pkh 120.3.12010000.1 2008/07/28 05:54:22 appldev ship $ */
3 /*
4 ==============================================================================
5 This package loads data into table per_za_employment_equity for use by
6 the Employment Equity Reports
7
8 MODIFICATION HISTORY
9 Name Date Version Bug Text
10 -------------- ----------- ------- ------- -----------------------------
11 R. Kingham 22 May 2000 110.0 Initial Version
12 R. Kingham 15 Jun 2000 110.1 Added Extra functionality for EEQ.
13 D. Son 20 Jun 2001 110.2 Changed package to suit multiple
14 Legal Entities and provide re-usable
15 functions.
16 F.D. Loubser 11 Sep 2001 115.3 Almost complete rewrite for 11i.
17 F.D. Loubser 10 Dec 2001 115.5 Business_group_id on user table
18 F.D. Loubser 1 Feb 2002 115.6 Added checkfile
19 F.D. Loubser 14 Feb 2002 115.7 Added multiple legal entity
20 F.D. Loubser 9 May 2002 115.8 g_cat_flex variable too small
21 Nageswara 18 Nov 2004 115.9 3962073 GSCC Warnings removed
22 R V Pahune 24-Jul-2006 115.12 5406242 Employment equity enhancement
23 ==============================================================================
24 */
25
26 -- This procedure resets the list of highest and lowest values.
27 procedure reset_high_low_lists;
28
29 -- This procedure returns the average of the 5 highes and lowest values from the lists.
30 procedure calc_highest_and_lowest_avg
31 (
32 p_high_avg out nocopy number,
33 p_low_avg out nocopy number
34 );
35
36 -- This procedure maintains a list of the 5 highest and lowest values passed to it.
37 procedure get_highest_and_lowest(p_value in number);
38
39 -- This function returns the number of days the assignment's status was Active Assignment
40 -- Note: Suspended Assignment is not seen as active in this case, since it is not
41 -- income generating
42 function get_active_days
43 (
44 p_assignment_id number,
45 p_report_start date,
46 p_report_end date
47 ) return number;
48
49 -- This function returns the termination reason from the user tables.
50 function get_termination_reason
51 (
52 p_business_group_id in per_all_assignments_f.business_group_id%type,
53 p_report_date in per_all_assignments_f.effective_end_date%type,
54 p_reason_code in per_periods_of_service.leaving_reason%type
55 ) return varchar2;
56
57 -- This procedure resets all the data structures for the Income Differentials report.
58 procedure reset_tables;
59
60 -- This function returns the average 5 highest paid employees per category or level.
61 function get_avg_5_highest_salary
62 (
63 p_report_date in per_all_assignments_f.effective_end_date%type,
64 p_business_group_id in per_all_assignments_f.business_group_id%type,
65 p_legent_param in per_assignment_extra_info.aei_information7%type := null,
66 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
67 p_occupational_level_cat in hr_lookups.meaning%type,
68 p_lookup_code in hr_lookups.lookup_code%type,
69 p_occupational_type in varchar2, -- CAT = Occupational Category , LEV = Occupational Level
70 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
71 ) return number;
72
73 -- This function returns the average 5 lowest paid employees per category or level.
74 function get_avg_5_lowest_salary
75 (
76 p_report_date in per_all_assignments_f.effective_end_date%type,
77 p_business_group_id in per_all_assignments_f.business_group_id%type,
78 p_legent_param in per_assignment_extra_info.aei_information7%type := null,
79 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
80 p_occupational_level_cat in hr_lookups.meaning%type,
81 p_lookup_code in hr_lookups.lookup_code%type,
82 p_occupational_type in varchar2, -- CAT = Occupational Category , LEV = Occupational Level
83 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
84 ) return number;
85
86 -- This function returns the person's legislated employment type (permanent or non-permanent)
87 function get_ee_employment_type_name
88 (
89 p_report_date in per_all_people_f.start_date%type,
90 p_period_of_service_id in per_all_assignments_f.period_of_service_id%type
91 ) return varchar2;
92
93 -- This function returns the occupational categories from the user tables.
94 function get_occupational_category
95 (
96 p_report_date in per_all_assignments_f.effective_end_date%type,
97 p_assignment_id in per_all_assignments_f.assignment_id%type,
98 p_job_id in per_all_assignments_f.job_id%type,
99 p_grade_id in per_all_assignments_f.grade_id%type,
100 p_position_id in per_all_assignments_f.position_id%type,
101 p_business_group_id in per_all_assignments_f.business_group_id%type
102 ) return varchar2;
103
104 -- This function returns the occupational levels from the user tables.
105 function get_occupational_level
106 (
107 p_report_date in per_all_assignments_f.effective_end_date%type,
108 p_assignment_id in per_all_assignments_f.assignment_id%type,
109 p_job_id in per_all_assignments_f.job_id%type,
110 p_grade_id in per_all_assignments_f.grade_id%type,
111 p_position_id in per_all_assignments_f.position_id%type,
112 p_business_group_id in per_all_assignments_f.business_group_id%type
113 ) return varchar2;
114
115 -- This function retrieves the occupational data via dynamic sql from the appropriate flexfield segment
116 function get_occupational_data
117 (
118 p_type in varchar2,
119 p_flex in varchar2,
120 p_segment in varchar2,
121 p_job_id in per_all_assignments_f.job_id%type,
122 p_grade_id in per_all_assignments_f.grade_id%type,
123 p_position_id in per_all_assignments_f.position_id%type
124 ) return varchar2;
125
126 -- This procedure caches the location of the occupational category and level data.
127 procedure cache_occupational_location
128 (
129 p_report_date in date,
130 p_business_group_id in per_all_assignments_f.business_group_id%type
131 );
132
133 -- This function returns the lookup_code from the user tables.
134 function get_lookup_code
135 (
136 p_meaning in hr_lookups.meaning%type
137 ) return varchar2;
138
139 -- This function populates an entity's sex and race and category matches.
140 procedure populate_ee_table
141 (
142 p_report_code in varchar2,
143 p_report_date in per_all_assignments_f.effective_end_date%type,
144 p_business_group_id in per_all_assignments_f.business_group_id%type,
145 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
146 );
147
148 -- This function retrieves the functional data via dynamic sql from the appropriate flexfield segment
149 function get_functional_data
150 (
151 p_flex in varchar2,
152 p_segment in varchar2,
153 p_job_id in per_all_assignments_f.job_id%type,
154 p_grade_id in per_all_assignments_f.grade_id%type,
155 p_position_id in per_all_assignments_f.position_id%type
156 ) return varchar2;
157
158
159 function get_functional_type
160 (
161 p_report_date in per_all_assignments_f.effective_end_date%type,
162 p_assignment_id in per_all_assignments_f.assignment_id%type,
163 p_job_id in per_all_assignments_f.job_id%type,
164 p_grade_id in per_all_assignments_f.grade_id%type,
165 p_position_id in per_all_assignments_f.position_id%type,
166 p_business_group_id in per_all_assignments_f.business_group_id%type
167 ) return varchar2;
168
169 -- Report will call this procedure which n terms call the populate_ee_table
170 procedure populate_ee_table_EEWF
171 (
172 p_report_date in per_all_assignments_f.effective_end_date%type,
173 p_business_group_id in per_all_assignments_f.business_group_id%type,
174 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
175 );
176
177
178 PROCEDURE ins_g_Enc_Diff_table(p_mi_inc IN number
179 , p_mc_inc IN number
180 , p_ma_inc IN number
181 , p_mw_inc IN number
182 , p_fa_inc IN number
183 , p_fc_inc IN number
184 , p_fi_inc IN number
185 , p_fw_inc IN number
186 , p_total_inc IN number
187 , p_ma IN number
188 , p_mc IN number
189 , p_mi IN number
190 , p_mw IN number
191 , p_fa IN number
192 , p_fc IN number
193 , p_fi IN number
194 , p_fw IN number
195 , p_total IN number
196 , p_cat_index IN number
197 , p_lev_index IN number
198 , p_legal_entity_id IN hr_all_organization_units.organization_id%type
199 , p_occupational_level IN hr_lookups.meaning%type
200 , p_occupational_category IN hr_lookups.meaning%type
201 , p_occupational_level_id IN hr_lookups.lookup_code%type
202 , p_occupational_category_id IN hr_lookups.lookup_code%type
203 );
204
205 Procedure cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
206 , p_occupational_level IN hr_lookups.meaning%type
207 , p_occupational_category IN hr_lookups.meaning%type
208 , p_race IN per_all_people_f.per_information4%type
209 , p_sex IN per_all_people_f.sex%type
210 , p_income IN number
211 , p_occupational_level_id IN hr_lookups.lookup_code%type
212 , p_occupational_category_id IN hr_lookups.lookup_code%type
213 ) ;
214
215 procedure init_g_cat_lev_table
216 (
217 p_report_date in per_all_assignments_f.effective_end_date%type,
218 p_business_group_id in per_all_assignments_f.business_group_id%type,
219 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
220 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
221 );
222
223
224 end per_za_employment_equity_pkg;
[Home] [Help]