[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_PERSON_M_SIZING
Source
1 PACKAGE BODY edw_hr_person_m_sizing AS
2 /* $Header: hriezpsn.pkb 120.1 2005/06/08 02:48:52 anmajumd noship $ */
3
4 /******************************************************************************/
5 /* Sets p_num_rows to the number of rows which would be collected between the */
6 /* given dates */
7 /******************************************************************************/
8 PROCEDURE count_source_rows( p_from_date IN DATE,
9 p_to_date IN DATE,
10 p_row_count OUT NOCOPY NUMBER )
11 IS
12
13 /* Selects count of rows from each union of collection view */
14 CURSOR row_count_cur IS
15 SELECT SUM(total)
16 FROM (
17 SELECT COUNT(*) total
18 FROM ( SELECT DISTINCT peo.person_id
19 FROM per_all_people_f peo
20 ,hr_all_organization_units bgr
21 WHERE peo.business_group_id = bgr.organization_id
22 AND greatest( NVL(peo.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
23 NVL(bgr.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
24 BETWEEN p_from_date AND p_to_date )
25 UNION ALL
26 SELECT COUNT(*) total
27 FROM ra_salesreps_all rs
28 ,hr_all_organization_units org
29 WHERE rs.org_id = org.organization_id (+)
30 AND greatest( NVL( rs.last_update_date,to_date('01-01-2000','DD-MM-YYYY')),
31 NVL(org.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
32 BETWEEN p_from_date AND p_to_date
33 UNION ALL
34 SELECT COUNT(*)
35 FROM mtl_planners mp
36 ,hr_all_organization_units org
37 WHERE mp.organization_id = org.organization_id
38 AND greatest( NVL(mp.last_update_date, to_date('01-01-2000','DD-MM-YYYY')),
39 NVL(org.last_update_date,to_date('01-01-2000','DD-MM-YYYY')))
40 BETWEEN p_from_date AND p_to_date
41 );
42
43 BEGIN
44
45 OPEN row_count_cur;
46 FETCH row_count_cur INTO p_row_count;
47 CLOSE row_count_cur;
48
49 END count_source_rows;
50
51 /******************************************************************************/
52 /* Estimates row lengths. Because the union is likely to be heavily skewed */
53 /* (since per_all_people_f is large and date-tracked) some attributes have */
54 /* been averaged across the union to improve the accuracy of the estimation */
55 /******************************************************************************/
56 PROCEDURE estimate_row_length( p_from_date IN DATE,
57 p_to_date IN DATE,
58 p_avg_row_length OUT NOCOPY NUMBER )
59 IS
60
61 /* Used to calculate weighted average across union */
62 x_peo_weight NUMBER;
63 x_ra_weight NUMBER;
64 x_mtl_weight NUMBER;
65
66 /* Length of a date attribute */
67 x_date NUMBER := 7;
68
69 /* Dimension attributes */
70 x_assignment_pk NUMBER := 0;
71 x_business_group NUMBER := 0;
72 x_creation_date NUMBER := x_date;
73 x_end_date NUMBER := x_date;
74 x_instance NUMBER := 0;
75 x_last_update_date NUMBER := x_date;
76 x_name NUMBER := 0;
77 x_start_date NUMBER := x_date;
78 x_national_identifier NUMBER := 0;
79 x_person_dp NUMBER := 0;
80 x_person_id NUMBER := 0;
81 x_person_num NUMBER := 0;
82 x_planner_code NUMBER := 0;
83 x_planner_flag NUMBER := 0;
84 x_previous_last_name NUMBER := 0;
85 x_region_of_birth NUMBER := 0;
86 x_rehire_rcmmndtn NUMBER := 0;
87 x_resume_exists NUMBER := 0;
88 x_resume_updated_date NUMBER := x_date;
89 x_salesrep_id NUMBER := 0;
90 x_sales_rep_flag NUMBER := 0;
91 x_student_status NUMBER := 0;
92 x_sys_gen_flag NUMBER := 0;
93 x_title NUMBER := 0;
94 x_town_of_birth NUMBER := 0;
95 x_buyer_flag NUMBER := 0;
96 x_country_of_birth NUMBER := 0;
97 x_crrspndnc_language NUMBER := 0;
98 x_date_emp_data_vrfd NUMBER := x_date;
99 x_date_of_birth NUMBER := x_date;
100 x_disability_flag NUMBER := 0;
101 x_effective_end_date NUMBER := x_date;
102 x_effective_start_date NUMBER := x_date;
103 x_email_address NUMBER := 0;
104 x_fast_path_employee NUMBER := 0;
105 x_first_name NUMBER := 0;
106 x_fte_capacity NUMBER := 0;
107 x_full_name NUMBER := 0;
108 x_gender NUMBER := 0;
109 x_global_person_id NUMBER := 0;
110 x_internal_location NUMBER := 0;
111 x_known_as NUMBER := 0;
112 x_last_name NUMBER := 0;
113 x_mailstop NUMBER := 0;
114 x_marital_status NUMBER := 0;
115 x_middle_names NUMBER := 0;
116 x_name_prefix NUMBER := 0;
117 x_name_suffix NUMBER := 0;
118 x_nationality NUMBER := 0;
119 x_employee_flag NUMBER := 0;
120 x_applicant_flag NUMBER := 0;
121 x_salesrep_number NUMBER := 0;
122 x_salesrep_name NUMBER := 0;
123 x_salesrep_org_id NUMBER := 0;
124 x_planner_description NUMBER := 0;
125 x_planner_org_id NUMBER := 0;
126
127
128 /* Selects the length of the instance code */
129 CURSOR inst_cur IS
130 SELECT avg(nvl(vsize(instance_code),0))
131 FROM edw_local_instance;
132
133 /*************/
134 /* HR Source */
135 /*************/
136
137 /* Length of an organization name */
138 CURSOR bgr_cur IS
139 SELECT avg(nvl(vsize(name),0))
140 FROM hr_all_organization_units
141 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
142
143 /* Length of person attributes - plus a count (weight) of size of table */
144 CURSOR peo_cur IS
145 SELECT
146 avg(nvl(vsize(person_id),0))
147 ,avg(nvl(vsize(last_name),0))
148 ,avg(nvl(vsize(NVL(applicant_number,employee_number)),0))
149 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',NVL(current_employee_flag,'N'))),0))
150 ,avg(nvl(vsize(email_address),0))
151 ,avg(nvl(vsize(first_name),0))
152 ,avg(nvl(vsize(full_name),0))
153 ,avg(nvl(vsize(known_as),0))
154 ,avg(nvl(vsize(hr_general.decode_lookup('MAR_STATUS',marital_status)),0))
155 ,avg(nvl(vsize(middle_names),0))
156 ,avg(nvl(vsize(hr_general.decode_lookup('NATIONALITY',nationality)),0))
157 ,avg(nvl(vsize(national_identifier),0))
158 ,avg(nvl(vsize(previous_last_name),0))
159 ,avg(nvl(vsize(sex),0))
160 ,avg(nvl(vsize(hr_general.decode_lookup('TITLE',title)),0))
161 ,avg(nvl(vsize(fte_capacity),0))
162 ,avg(nvl(vsize(internal_location),0))
163 ,avg(nvl(vsize(mailstop),0))
164 ,avg(nvl(vsize(pre_name_adjunct),0))
165 ,avg(nvl(vsize(hr_general.decode_lookup('STUDENT_STATUS',student_status)),0))
166 ,avg(nvl(vsize(suffix),0))
167 ,avg(nvl(vsize(town_of_birth),0))
168 ,avg(nvl(vsize(region_of_birth),0))
169 ,avg(nvl(vsize(country_of_birth),0))
170 ,avg(nvl(vsize(global_person_id),0))
171 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',rehire_recommendation)),0))
172 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',registered_disabled_flag)),0))
173 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',resume_exists)),0))
174 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO',fast_path_employee)),0))
175 ,count(person_id)
176 FROM
177 per_all_people_f peo
178 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
179
180 /* Length of language */
181 CURSOR lng_cur IS
182 SELECT avg(nvl(vsize(nls_language),0))
183 FROM fnd_languages
184 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
185
186 /*************/
187 /* RA Source */
188 /*************/
189
190 /* Length of salesrep attributes - plus a weight */
191 CURSOR ra_cur IS
192 SELECT
193 avg(nvl(vsize(salesrep_id),0))
194 ,avg(nvl(vsize(salesrep_number),0))
195 ,avg(nvl(vsize(name),0))
196 ,avg(nvl(vsize(org_id),0))
197 ,count(salesrep_id)
198 FROM ra_salesreps_all
199 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
200
201 /**************/
202 /* MTL Source */
203 /**************/
204
205 /* Lenghth of planner attributes - plus a weight */
206 CURSOR mtl_cur IS
207 SELECT
208 avg(nvl(vsize(planner_code),0))
209 ,avg(nvl(vsize(description),0))
210 ,avg(nvl(vsize(organization_id),0))
211 ,count(planner_code)
212 FROM mtl_planners
213 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
214
215
216 BEGIN
217
218
219 OPEN inst_cur;
220 FETCH inst_cur INTO x_instance;
221 CLOSE inst_cur;
222
223 /*************/
224 /* HR Source */
225 /*************/
226
227 OPEN bgr_cur;
228 FETCH bgr_cur INTO x_business_group;
229 CLOSE bgr_cur;
230
231 OPEN peo_cur;
232 FETCH peo_cur INTO
233 x_person_id,
234 x_last_name,
235 x_person_num,
236 x_employee_flag,
237 x_email_address,
238 x_first_name,
239 x_full_name,
240 x_known_as,
241 x_marital_status,
242 x_middle_names,
243 x_nationality,
244 x_national_identifier,
245 x_previous_last_name,
246 x_gender,
247 x_title,
248 x_fte_capacity,
249 x_internal_location,
250 x_mailstop,
251 x_name_prefix,
252 x_student_status,
253 x_name_suffix,
254 x_town_of_birth,
255 x_region_of_birth,
256 x_country_of_birth,
257 x_global_person_id,
258 x_rehire_rcmmndtn,
259 x_disability_flag,
260 x_resume_exists,
261 x_fast_path_employee,
262 x_peo_weight;
263 CLOSE peo_cur;
264
265 OPEN lng_cur;
266 FETCH lng_cur INTO x_crrspndnc_language;
267 CLOSE lng_cur;
268
269 x_assignment_pk := x_person_id + x_instance;
270 x_name := x_full_name + x_person_num;
271
272
273 /* Yes-No flags should all be similar if the length of "Yes" and "No" is close */
274 x_planner_flag := x_employee_flag;
275 x_sales_rep_flag := x_employee_flag;
276 x_sys_gen_flag := x_employee_flag;
277 x_buyer_flag := x_employee_flag;
278 x_applicant_flag := x_employee_flag;
279
280 /*************/
281 /* RA Source */
282 /*************/
283 OPEN ra_cur;
284 FETCH ra_cur INTO
285 x_salesrep_id,
286 x_salesrep_number,
287 x_salesrep_name,
288 x_salesrep_org_id,
289 x_ra_weight;
290 CLOSE ra_cur;
291
292 x_assignment_pk := GREATEST(x_assignment_pk,
293 x_salesrep_id + x_salesrep_org_id + x_instance);
294 x_name := (((x_name*x_peo_weight)
295 + ((x_salesrep_name + x_business_group)*x_ra_weight))
296 / (x_peo_weight + x_ra_weight));
297 x_full_name := (((x_full_name*x_peo_weight)
298 + (x_salesrep_name*x_ra_weight))
299 / (x_peo_weight + x_ra_weight));
300
301
302 /**************/
303 /* MTL Source */
304 /**************/
305 OPEN mtl_cur;
306 FETCH mtl_cur INTO
307 x_planner_code,
308 x_planner_description,
309 x_planner_org_id,
310 x_mtl_weight;
311 CLOSE mtl_cur;
312
313 x_assignment_pk := GREATEST(x_assignment_pk,
314 x_planner_code + x_planner_org_id + x_instance);
315 x_full_name := (((x_full_name*(x_peo_weight+x_ra_weight))
316 + (x_planner_description*x_mtl_weight))
317 / (x_peo_weight + x_ra_weight + x_mtl_weight));
318 x_name := (((x_name*(x_peo_weight+x_ra_weight))
319 + ((x_planner_code + x_business_group)*x_mtl_weight))
320 / (x_peo_weight + x_ra_weight + x_mtl_weight));
321 x_person_dp := x_name;
322
323 p_avg_row_length :=
324 ( NVL(ceil(x_assignment_pk + 1), 0)
325 + NVL(ceil(x_business_group + 1), 0)
326 + NVL(ceil(x_creation_date + 1), 0)
327 + NVL(ceil(x_end_date + 1), 0)
328 + NVL(ceil(x_instance + 1), 0)
329 + NVL(ceil(x_last_update_date + 1), 0)
330 + NVL(ceil(x_name + 1), 0)
331 + NVL(ceil(x_start_date + 1), 0)
332 + NVL(ceil(x_national_identifier + 1), 0)
333 + NVL(ceil(x_person_dp + 1), 0)
334 + NVL(ceil(x_person_id + 1), 0)
335 + NVL(ceil(x_person_num + 1), 0)
336 + NVL(ceil(x_planner_code + 1), 0)
337 + NVL(ceil(x_planner_flag + 1), 0)
338 + NVL(ceil(x_previous_last_name + 1), 0)
339 + NVL(ceil(x_region_of_birth + 1), 0)
340 + NVL(ceil(x_rehire_rcmmndtn + 1), 0)
341 + NVL(ceil(x_resume_exists + 1), 0)
342 + NVL(ceil(x_resume_updated_date + 1), 0)
343 + NVL(ceil(x_salesrep_id + 1), 0)
344 + NVL(ceil(x_sales_rep_flag + 1), 0)
345 + NVL(ceil(x_student_status + 1), 0)
346 + NVL(ceil(x_sys_gen_flag + 1), 0)
347 + NVL(ceil(x_title + 1), 0)
348 + NVL(ceil(x_town_of_birth + 1), 0)
349 + NVL(ceil(x_buyer_flag + 1), 0)
350 + NVL(ceil(x_country_of_birth + 1), 0)
351 + NVL(ceil(x_crrspndnc_language + 1), 0)
352 + NVL(ceil(x_date_emp_data_vrfd + 1), 0)
353 + NVL(ceil(x_date_of_birth + 1), 0)
354 + NVL(ceil(x_disability_flag + 1), 0)
355 + NVL(ceil(x_effective_end_date + 1), 0)
356 + NVL(ceil(x_effective_start_date + 1), 0)
357 + NVL(ceil(x_email_address + 1), 0)
358 + NVL(ceil(x_fast_path_employee + 1), 0)
359 + NVL(ceil(x_first_name + 1), 0)
360 + NVL(ceil(x_fte_capacity + 1), 0)
361 + NVL(ceil(x_full_name + 1), 0)
362 + NVL(ceil(x_gender + 1), 0)
363 + NVL(ceil(x_global_person_id + 1), 0)
364 + NVL(ceil(x_internal_location + 1), 0)
365 + NVL(ceil(x_known_as + 1), 0)
366 + NVL(ceil(x_last_name + 1), 0)
367 + NVL(ceil(x_mailstop + 1), 0)
368 + NVL(ceil(x_marital_status + 1), 0)
369 + NVL(ceil(x_middle_names + 1), 0)
370 + NVL(ceil(x_name_prefix + 1), 0)
371 + NVL(ceil(x_name_suffix + 1), 0)
372 + NVL(ceil(x_nationality + 1), 0)
373 + NVL(ceil(x_employee_flag + 1), 0)
374 + NVL(ceil(x_applicant_flag + 1), 0) );
375
376 END estimate_row_length;
377
378 END edw_hr_person_m_sizing;