1 package per_za_employment_equity_pkg AUTHID CURRENT_USER as
2 /* $Header: perzaeer.pkh 120.6.12020000.1 2012/06/29 02:19:48 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 R Babla 24-Nov-2009 115.13 9112237 Added init_g_cat_lev_new_table and
27 reporting year 2009
24 supporting procedures from
25 reporting year 2009
26 NCHINNAM 01-Dec-2009 115.14 9112237 Added new procedures from
28 R Babla 01-Apr-2010 115.16 9462039 Modified few procedures to add
29 parameter p_year
30 ==============================================================================
31 */
32
33 -- This procedure resets the list of highest and lowest values.
34 procedure reset_high_low_lists;
35
36 -- This procedure returns the average of the 5 highes and lowest values from the lists.
37 procedure calc_highest_and_lowest_avg
38 (
39 p_high_avg out nocopy number,
40 p_low_avg out nocopy number
41 );
42
43 -- This procedure maintains a list of the 5 highest and lowest values passed to it.
44 procedure get_highest_and_lowest(p_value in number);
45
46 -- This function returns the number of days the assignment's status was Active Assignment
47 -- Note: Suspended Assignment is not seen as active in this case, since it is not
48 -- income generating
49 function get_active_days
50 (
51 p_assignment_id number,
52 p_report_start date,
53 p_report_end date
54 ) return number;
55
56 -- This function returns the termination reason from the user tables.
57 function get_termination_reason
58 (
59 p_business_group_id in per_all_assignments_f.business_group_id%type,
60 p_report_date in per_all_assignments_f.effective_end_date%type,
61 p_reason_code in per_periods_of_service.leaving_reason%type
62 ) return varchar2;
63
64 -- This procedure resets all the data structures for the Income Differentials report.
65 procedure reset_tables;
66
67 -- This function returns the average 5 highest paid employees per category or level.
68 function get_avg_5_highest_salary
69 (
70 p_report_date in per_all_assignments_f.effective_end_date%type,
71 p_business_group_id in per_all_assignments_f.business_group_id%type,
72 p_legent_param in per_assignment_extra_info.aei_information7%type := null,
73 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
74 p_occupational_level_cat in hr_lookups.meaning%type,
75 p_lookup_code in hr_lookups.lookup_code%type,
76 p_occupational_type in varchar2, -- CAT = Occupational Category , LEV = Occupational Level
77 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
78 ) return number;
79
80 -- This function returns the average 5 lowest paid employees per category or level.
81 function get_avg_5_lowest_salary
82 (
83 p_report_date in per_all_assignments_f.effective_end_date%type,
84 p_business_group_id in per_all_assignments_f.business_group_id%type,
85 p_legent_param in per_assignment_extra_info.aei_information7%type := null,
86 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
87 p_occupational_level_cat in hr_lookups.meaning%type,
88 p_lookup_code in hr_lookups.lookup_code%type,
89 p_occupational_type in varchar2, -- CAT = Occupational Category , LEV = Occupational Level
90 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
91 ) return number;
92
93 -- This function returns the person's legislated employment type (permanent or non-permanent)
94 function get_ee_employment_type_name
95 (
96 p_report_date in per_all_people_f.start_date%type,
97 p_period_of_service_id in per_all_assignments_f.period_of_service_id%type
98 ) return varchar2;
99
100 -- This function returns the occupational categories from the user tables.
101 function get_occupational_category
102 (
103 p_report_date in per_all_assignments_f.effective_end_date%type,
104 p_assignment_id in per_all_assignments_f.assignment_id%type,
105 p_job_id in per_all_assignments_f.job_id%type,
106 p_grade_id in per_all_assignments_f.grade_id%type,
107 p_position_id in per_all_assignments_f.position_id%type,
108 p_business_group_id in per_all_assignments_f.business_group_id%type
109 ) return varchar2;
110
111 -- This function returns the occupational levels from the user tables.
112 function get_occupational_level
113 (
114 p_report_date in per_all_assignments_f.effective_end_date%type,
115 p_assignment_id in per_all_assignments_f.assignment_id%type,
116 p_job_id in per_all_assignments_f.job_id%type,
117 p_grade_id in per_all_assignments_f.grade_id%type,
118 p_position_id in per_all_assignments_f.position_id%type,
119 p_business_group_id in per_all_assignments_f.business_group_id%type,
120 p_year in number default 2000
121 ) return varchar2;
122
123 -- This function retrieves the occupational data via dynamic sql from the appropriate flexfield segment
124 function get_occupational_data
125 (
126 p_type in varchar2,
127 p_flex in varchar2,
128 p_segment in varchar2,
129 p_job_id in per_all_assignments_f.job_id%type,
130 p_grade_id in per_all_assignments_f.grade_id%type,
131 p_position_id in per_all_assignments_f.position_id%type
132 ) return varchar2;
133
134 -- This procedure caches the location of the occupational category and level data.
135 procedure cache_occupational_location
136 (
137 p_report_date in date,
141
138 p_business_group_id in per_all_assignments_f.business_group_id%type,
139 p_year in number default 2000
140 );
142 -- This function returns the lookup_code from the user tables.
143 function get_lookup_code
144 (
145 p_meaning in hr_lookups.meaning%type
146 ) return varchar2;
147
148 -- This function populates an entity's sex and race and category matches.
149 procedure populate_ee_table
150 (
151 p_report_code in varchar2,
152 p_report_date in per_all_assignments_f.effective_end_date%type,
153 p_business_group_id in per_all_assignments_f.business_group_id%type,
154 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
155 );
156
157 -- This function retrieves the functional data via dynamic sql from the appropriate flexfield segment
158 function get_functional_data
159 (
160 p_flex in varchar2,
161 p_segment in varchar2,
162 p_job_id in per_all_assignments_f.job_id%type,
163 p_grade_id in per_all_assignments_f.grade_id%type,
164 p_position_id in per_all_assignments_f.position_id%type
165 ) return varchar2;
166
167
168 function get_functional_type
169 (
170 p_report_date in per_all_assignments_f.effective_end_date%type,
171 p_assignment_id in per_all_assignments_f.assignment_id%type,
172 p_job_id in per_all_assignments_f.job_id%type,
173 p_grade_id in per_all_assignments_f.grade_id%type,
174 p_position_id in per_all_assignments_f.position_id%type,
175 p_business_group_id in per_all_assignments_f.business_group_id%type,
176 p_year in number default 2000
177 ) return varchar2;
178
179 -- Report will call this procedure which n terms call the populate_ee_table
180 procedure populate_ee_table_EEWF
181 (
182 p_report_date in per_all_assignments_f.effective_end_date%type,
183 p_business_group_id in per_all_assignments_f.business_group_id%type,
184 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
185 );
186
187
188 PROCEDURE ins_g_Enc_Diff_table(p_mi_inc IN number
189 , p_mc_inc IN number
190 , p_ma_inc IN number
191 , p_mw_inc IN number
192 , p_fa_inc IN number
193 , p_fc_inc IN number
194 , p_fi_inc IN number
195 , p_fw_inc IN number
196 , p_total_inc IN number
197 , p_ma IN number
198 , p_mc IN number
199 , p_mi IN number
200 , p_mw IN number
201 , p_fa IN number
202 , p_fc IN number
203 , p_fi IN number
204 , p_fw IN number
205 , p_total IN number
206 , p_cat_index IN number
207 , p_lev_index IN number
208 , p_legal_entity_id IN hr_all_organization_units.organization_id%type
209 , p_occupational_level IN hr_lookups.meaning%type
210 , p_occupational_category IN hr_lookups.meaning%type
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 cat_lev_data ( p_legal_entity_id IN hr_all_organization_units.organization_id%type
216 , p_occupational_level IN hr_lookups.meaning%type
217 , p_occupational_category IN hr_lookups.meaning%type
218 , p_race IN per_all_people_f.per_information4%type
219 , p_sex IN per_all_people_f.sex%type
220 , p_income IN number
221 , p_occupational_level_id IN hr_lookups.lookup_code%type
222 , p_occupational_category_id IN hr_lookups.lookup_code%type
223 ) ;
224
225 procedure init_g_cat_lev_table
226 (
227 p_report_date in per_all_assignments_f.effective_end_date%type,
228 p_business_group_id in per_all_assignments_f.business_group_id%type,
229 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
230 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
231 );
232
233 procedure init_g_cat_lev_new_table
234 (
235 p_report_date in per_all_assignments_f.effective_end_date%type,
236 p_business_group_id in per_all_assignments_f.business_group_id%type,
237 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null,
238 p_salary_method in varchar2 -- SAL = Salary Basis Method, BAL = Payroll Balances Method
239 );
240
241 function get_termination_reason_new
242 (
243 p_business_group_id in per_all_assignments_f.business_group_id%type,
244 p_report_date in per_all_assignments_f.effective_end_date%type,
245 p_reason_code in per_periods_of_service.leaving_reason%type
246 ) return varchar2;
247
248 procedure populate_ee_table_new
249 (
250 p_report_code in varchar2,
251 p_report_date in per_all_assignments_f.effective_end_date%type,
252 p_business_group_id in per_all_assignments_f.business_group_id%type,
253 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
254 );
255
256 procedure populate_ee_table_EEWF_new
257 (
258 p_report_date in per_all_assignments_f.effective_end_date%type,
259 p_business_group_id in per_all_assignments_f.business_group_id%type,
260 p_legal_entity_id in per_assignment_extra_info.aei_information7%type := null
261 );
262
263 end per_za_employment_equity_pkg;
[Home] [Help]