[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_ACTVTY_F_SIZING
Source
1 PACKAGE BODY hr_edw_wrk_actvty_f_sizing AS
2 /* $Header: hriezwac.pkb 120.1 2005/06/08 02:51:16 anmajumd noship $ */
3 /******************************************************************************/
4 /* Sets p_row_count to the number of rows which would be collected between */
5 /* the given dates */
6 /******************************************************************************/
7 PROCEDURE count_source_rows( p_from_date IN DATE,
8 p_to_date IN DATE,
9 p_row_count OUT NOCOPY NUMBER )
10 IS
11
12 /* Cursor description */
13 CURSOR row_count_cur IS
14 SELECT count(asg.assignment_id) total
15 FROM per_all_assignments_f asg
16 WHERE NVL(asg.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
17 BETWEEN p_from_date AND p_to_date;
18
19 BEGIN
20
21 OPEN row_count_cur;
22 FETCH row_count_cur INTO p_row_count;
23 CLOSE row_count_cur;
24
25 END count_source_rows;
26
27
28 /******************************************************************************/
29 /* Estimates row lengths. */
30 /******************************************************************************/
31 PROCEDURE estimate_row_length( p_from_date IN DATE,
32 p_to_date IN DATE,
33 p_avg_row_length OUT NOCOPY NUMBER )
34
35 IS
36
37 x_total_wac NUMBER;
38
39 x_date NUMBER:=7;
40
41 x_assignment_change_pk NUMBER:=0;
42
43 x_age_band_fk NUMBER:=0;
44 x_application_fk NUMBER:=0;
45 x_assignment_fk NUMBER:=0;
46 x_assignment_change_fk NUMBER:=0;
47 x_geography_from_fk NUMBER:=0;
48 x_geography_to_fk NUMBER:=0;
49 x_grade_from_fk NUMBER:=0;
50 x_grade_to_fk NUMBER:=0;
51 x_instance_fk NUMBER:=0;
52 x_job_from_fk NUMBER:=0;
53 x_job_to_fk NUMBER:=0;
54 x_organization_from_fk NUMBER:=0;
55 x_organization_to_fk NUMBER:=0;
56 x_person_fk NUMBER:=0;
57 x_person_type_fk NUMBER:=0;
58 x_position_from_fk NUMBER:=0;
59 x_position_to_fk NUMBER:=0;
60 x_movement_fk NUMBER:=0;
61 x_reason_fk NUMBER:=0;
62 x_service_band_fk NUMBER:=0;
63 x_time_from_fk NUMBER:=0;
64 x_time_to_fk NUMBER:=0;
65
66 x_last_update_date NUMBER:=x_date;
67 x_creation_date NUMBER:=x_date;
68 x_effective_start_date NUMBER:=x_date;
69 x_effective_end_date NUMBER:=x_date;
70
71 x_assignment_id NUMBER:=0;
72 x_business_group_id NUMBER:=0;
73 x_recruiter_id NUMBER:=0;
74 x_grade_id NUMBER:=0;
75 x_position_id NUMBER:=0;
76 x_job_id NUMBER:=0;
77 x_assignment_status_type_id NUMBER:=0;
78 x_payroll_id NUMBER:=0;
79 x_location_id NUMBER:=0;
80 x_person_referred_by_id NUMBER:=0;
81 x_supervisor_id NUMBER:=0;
82 x_special_ceiling_step_id NUMBER:=0;
83 x_person_id NUMBER:=0;
84 x_recruitment_activity_id NUMBER:=0;
85 x_source_organization_id NUMBER:=0;
86 x_organization_id NUMBER:=0;
87 x_people_group_id NUMBER:=0;
88 x_soft_coding_keyflex_id NUMBER:=0;
89 x_vacancy_id NUMBER:=0;
90 x_pay_basis_id NUMBER:=0;
91 x_assignment_sequence NUMBER:=0;
92 x_assignment_type NUMBER:=0;
93 x_primary_flag NUMBER:=0;
94 x_application_id NUMBER:=0;
95 x_assignment_number NUMBER:=0;
96 x_change_reason NUMBER:=0;
97 x_comment_id NUMBER:=0;
98 x_date_probation_end NUMBER:=x_date;
99 x_default_code_comb_id NUMBER:=0;
100 x_employment_category NUMBER:=0;
101 x_frequency NUMBER:=0;
102 x_internal_address_line NUMBER:=0;
103 x_manager_flag NUMBER:=0;
104 x_normal_hours NUMBER:=0;
105 x_perf_review_period NUMBER:=0;
106 x_perf_review_period_frequency NUMBER:=0;
107 x_period_of_service_id NUMBER:=0;
108 x_probation_period NUMBER:=0;
109 x_probation_unit NUMBER:=0;
110 x_sal_review_period NUMBER:=0;
111 x_sal_review_period_frequency NUMBER:=0;
112 x_set_of_books_id NUMBER:=0;
113 x_source_type NUMBER:=0;
114 x_time_normal_finish NUMBER:=0;
115 x_time_normal_start NUMBER:=0;
116 x_asg_request_id NUMBER:=0;
117 x_program_application_id NUMBER:=0;
118 x_program_id NUMBER:=0;
119 x_program_update_date NUMBER:=x_date;
120 x_asg_title NUMBER:=0;
121 x_object_version_number NUMBER:=0;
122 x_bargaining_unit_code NUMBER:=0;
123 x_labour_union_member_flag NUMBER:=0;
124 x_hourly_salaried_code NUMBER:=0;
125 x_contract_id NUMBER:=0;
126 x_collective_agreement_id NUMBER:=0;
127 x_cagr_id_flex_num NUMBER:=0;
128 x_cagr_grade_def_id NUMBER:=0;
129 x_establishment_id NUMBER:=0;
130
131 /* Select the length of the instance code */
132 CURSOR inst_cur IS
133 SELECT avg(nvl( vsize(instance_code),0 ))
134 FROM edw_local_instance;
135
136 CURSOR asg_cur IS
137 SELECT
138 avg(nvl(vsize(asg.assignment_id),0))
139 ,avg(nvl(vsize(asg.effective_start_date),0))
140 ,avg(nvl(vsize(asg.effective_end_date),0))
141 ,avg(nvl(vsize(asg.business_group_id),0))
142 ,avg(nvl(vsize(asg.recruiter_id),0))
143 ,avg(nvl(vsize(asg.grade_id),0))
144 ,avg(nvl(vsize(asg.position_id),0))
145 ,avg(nvl(vsize(asg.job_id),0))
146 ,avg(nvl(vsize(asg.assignment_status_type_id),0))
147 ,avg(nvl(vsize(asg.payroll_id),0))
148 ,avg(nvl(vsize(asg.location_id),0))
149 ,avg(nvl(vsize(asg.person_referred_by_id),0))
150 ,avg(nvl(vsize(asg.supervisor_id),0))
151 ,avg(nvl(vsize(asg.special_ceiling_step_id),0))
152 ,avg(nvl(vsize(asg.person_id),0))
153 ,avg(nvl(vsize(asg.recruitment_activity_id),0))
154 ,avg(nvl(vsize(asg.source_organization_id),0))
155 ,avg(nvl(vsize(asg.organization_id),0))
156 ,avg(nvl(vsize(asg.people_group_id),0))
157 ,avg(nvl(vsize(asg.soft_coding_keyflex_id),0))
158 ,avg(nvl(vsize(asg.vacancy_id),0))
159 ,avg(nvl(vsize(asg.pay_basis_id),0))
160 ,avg(nvl(vsize(asg.assignment_sequence),0))
161 ,avg(nvl(vsize(asg.assignment_type),0))
162 ,avg(nvl(vsize(asg.primary_flag),0))
163 ,avg(nvl(vsize(asg.application_id),0))
164 ,avg(nvl(vsize(asg.assignment_number),0))
165 ,avg(nvl(vsize(asg.change_reason),0))
166 ,avg(nvl(vsize(asg.comment_id),0))
167 ,avg(nvl(vsize(asg.date_probation_end),0))
168 ,avg(nvl(vsize(asg.default_code_comb_id),0))
169 ,avg(nvl(vsize(asg.employment_category),0))
170 ,avg(nvl(vsize(asg.frequency),0))
171 ,avg(nvl(vsize(asg.internal_address_line),0))
172 ,avg(nvl(vsize(asg.manager_flag),0))
173 ,avg(nvl(vsize(asg.normal_hours),0))
174 ,avg(nvl(vsize(asg.perf_review_period),0))
175 ,avg(nvl(vsize(asg.perf_review_period_frequency),0))
176 ,avg(nvl(vsize(asg.period_of_service_id),0))
177 ,avg(nvl(vsize(asg.probation_period),0))
178 ,avg(nvl(vsize(asg.probation_unit),0))
179 ,avg(nvl(vsize(asg.sal_review_period),0))
180 ,avg(nvl(vsize(asg.sal_review_period_frequency),0))
181 ,avg(nvl(vsize(asg.set_of_books_id),0))
182 ,avg(nvl(vsize(asg.source_type),0))
183 ,avg(nvl(vsize(asg.time_normal_finish),0))
184 ,avg(nvl(vsize(asg.time_normal_start),0))
185 ,avg(nvl(vsize(asg.request_id),0))
186 ,avg(nvl(vsize(asg.program_application_id),0))
187 ,avg(nvl(vsize(asg.program_id),0))
188 ,avg(nvl(vsize(asg.program_update_date),0))
189 ,avg(nvl(vsize(asg.title),0))
190 ,avg(nvl(vsize(asg.object_version_number),0))
191 ,avg(nvl(vsize(asg.bargaining_unit_code),0))
192 ,avg(nvl(vsize(asg.labour_union_member_flag),0))
193 ,avg(nvl(vsize(asg.hourly_salaried_code),0))
194 ,avg(nvl(vsize(asg.contract_id),0))
195 ,avg(nvl(vsize(asg.collective_agreement_id),0))
196 ,avg(nvl(vsize(asg.cagr_id_flex_num),0))
197 ,avg(nvl(vsize(asg.cagr_grade_def_id),0))
198 ,avg(nvl(vsize(asg.establishment_id),0))
199 FROM per_all_assignments_f asg
200 WHERE asg.last_update_date BETWEEN p_from_date AND p_to_date;
201
202 BEGIN
203
204 OPEN inst_cur;
205 FETCH inst_cur INTO x_instance_fk;
206 CLOSE inst_cur;
207
208 OPEN asg_cur;
209 FETCH asg_cur INTO
210 x_assignment_id
211 ,x_effective_start_date
212 ,x_effective_end_date
213 ,x_business_group_id
214 ,x_recruiter_id
215 ,x_grade_id
216 ,x_position_id
217 ,x_job_id
218 ,x_assignment_status_type_id
219 ,x_payroll_id
220 ,x_location_id
221 ,x_person_referred_by_id
222 ,x_supervisor_id
223 ,x_special_ceiling_step_id
224 ,x_person_id
225 ,x_recruitment_activity_id
226 ,x_source_organization_id
227 ,x_organization_id
228 ,x_people_group_id
229 ,x_soft_coding_keyflex_id
230 ,x_vacancy_id
231 ,x_pay_basis_id
232 ,x_assignment_sequence
233 ,x_assignment_type
234 ,x_primary_flag
235 ,x_application_id
236 ,x_assignment_number
237 ,x_change_reason
238 ,x_comment_id
239 ,x_date_probation_end
240 ,x_default_code_comb_id
241 ,x_employment_category
242 ,x_frequency
243 ,x_internal_address_line
244 ,x_manager_flag
245 ,x_normal_hours
246 ,x_perf_review_period
247 ,x_perf_review_period_frequency
248 ,x_period_of_service_id
249 ,x_probation_period
250 ,x_probation_unit
251 ,x_sal_review_period
252 ,x_sal_review_period_frequency
253 ,x_set_of_books_id
254 ,x_source_type
255 ,x_time_normal_finish
256 ,x_time_normal_start
257 ,x_asg_request_id
258 ,x_program_application_id
259 ,x_program_id
260 ,x_program_update_date
261 ,x_asg_title
262 ,x_object_version_number
263 ,x_bargaining_unit_code
264 ,x_labour_union_member_flag
265 ,x_hourly_salaried_code
266 ,x_contract_id
267 ,x_collective_agreement_id
268 ,x_cagr_id_flex_num
269 ,x_cagr_grade_def_id
270 ,x_establishment_id;
271 CLOSE asg_cur;
272
273 x_assignment_change_pk := x_assignment_id + x_effective_start_date + x_instance_fk;
274 x_age_band_fk := hri_edw_dim_sizing.get_size_agb_pk;
275 x_assignment_fk := hri_edw_dim_sizing.get_size_asg_pk;
276 x_assignment_change_fk := hri_edw_dim_sizing.get_size_acg_pk;
277 x_geography_from_fk := hri_edw_dim_sizing.get_size_geog_pk;
278 x_geography_to_fk := x_geography_from_fk;
279 x_grade_from_fk := hri_edw_dim_sizing.get_size_grd_pk;
280 x_grade_to_fk := x_grade_from_fk;
281 x_job_from_fk := hri_edw_dim_sizing.get_size_job_pk;
282 x_job_to_fk := x_job_from_fk;
283 x_organization_from_fk := hri_edw_dim_sizing.get_size_org_pk;
284 x_organization_to_fk := x_organization_from_fk;
285 x_person_fk := hri_edw_dim_sizing.get_size_psn_pk;
286 x_person_type_fk := hri_edw_dim_sizing.get_size_pty_pk;
287 x_position_from_fk := hri_edw_dim_sizing.get_size_pos_pk;
288 x_position_to_fk := x_position_from_fk;
289 x_movement_fk := hri_edw_dim_sizing.get_size_mvt_pk;
290 x_reason_fk := hri_edw_dim_sizing.get_size_rsn_pk;
291 x_service_band_fk := hri_edw_dim_sizing.get_size_lwb_pk;
292 x_time_from_fk := hri_edw_dim_sizing.get_size_time_pk;
293 x_time_to_fk := x_time_from_fk;
294
295
296
297 x_total_wac := NVL(ceil(x_assignment_change_pk + 1), 0)
298 + NVL(ceil(x_age_band_fk + 1), 0)
299 + NVL(ceil(x_application_fk + 1), 0)
300 + NVL(ceil(x_assignment_fk + 1), 0)
301 + NVL(ceil(x_assignment_change_fk + 1), 0)
302 + NVL(ceil(x_geography_from_fk + 1), 0)
303 + NVL(ceil(x_geography_to_fk + 1), 0)
304 + NVL(ceil(x_grade_from_fk + 1), 0)
305 + NVL(ceil(x_grade_to_fk + 1), 0)
306 + NVL(ceil(x_instance_fk + 1), 0)
307 + NVL(ceil(x_job_from_fk + 1), 0)
308 + NVL(ceil(x_job_to_fk + 1), 0)
309 + NVL(ceil(x_organization_from_fk + 1), 0)
310 + NVL(ceil(x_organization_to_fk + 1), 0)
311 + NVL(ceil(x_person_fk + 1), 0)
312 + NVL(ceil(x_person_type_fk + 1), 0)
313 + NVL(ceil(x_position_from_fk + 1), 0)
314 + NVL(ceil(x_position_to_fk + 1), 0)
315 + NVL(ceil(x_movement_fk + 1), 0)
316 + NVL(ceil(x_reason_fk + 1), 0)
317 + NVL(ceil(x_service_band_fk + 1), 0)
318 + NVL(ceil(x_time_from_fk + 1), 0)
319 + NVL(ceil(x_time_to_fk + 1), 0)
320 + NVL(ceil(x_last_update_date + 1), 0)
321 + NVL(ceil(x_creation_date + 1), 0)
322 + NVL(ceil(x_assignment_id + 1), 0)
323 + NVL(ceil(x_effective_start_date + 1), 0)
324 + NVL(ceil(x_effective_end_date + 1), 0)
325 + NVL(ceil(x_business_group_id + 1), 0)
326 + NVL(ceil(x_recruiter_id + 1), 0)
327 + NVL(ceil(x_grade_id + 1), 0)
328 + NVL(ceil(x_position_id + 1), 0)
329 + NVL(ceil(x_job_id + 1), 0)
330 + NVL(ceil(x_assignment_status_type_id + 1), 0)
331 + NVL(ceil(x_payroll_id + 1), 0)
332 + NVL(ceil(x_location_id + 1), 0)
333 + NVL(ceil(x_person_referred_by_id + 1), 0)
334 + NVL(ceil(x_supervisor_id + 1), 0)
335 + NVL(ceil(x_special_ceiling_step_id + 1), 0)
336 + NVL(ceil(x_person_id + 1), 0)
337 + NVL(ceil(x_recruitment_activity_id + 1), 0)
338 + NVL(ceil(x_source_organization_id + 1), 0)
339 + NVL(ceil(x_organization_id + 1), 0)
340 + NVL(ceil(x_people_group_id + 1), 0)
341 + NVL(ceil(x_soft_coding_keyflex_id + 1), 0)
342 + NVL(ceil(x_pay_basis_id + 1), 0)
343 + NVL(ceil(x_assignment_sequence + 1), 0)
344 + NVL(ceil(x_assignment_type + 1), 0)
345 + NVL(ceil(x_primary_flag + 1), 0)
346 + NVL(ceil(x_application_id + 1), 0)
347 + NVL(ceil(x_assignment_number + 1), 0)
348 + NVL(ceil(x_change_reason + 1), 0)
349 + NVL(ceil(x_comment_id + 1), 0)
350 + NVL(ceil(x_date_probation_end + 1), 0)
351 + NVL(ceil(x_default_code_comb_id + 1), 0)
352 + NVL(ceil(x_employment_category + 1), 0)
353 + NVL(ceil(x_frequency + 1), 0)
354 + NVL(ceil(x_internal_address_line + 1), 0)
355 + NVL(ceil(x_manager_flag + 1), 0)
356 + NVL(ceil(x_normal_hours + 1), 0)
357 + NVL(ceil(x_perf_review_period + 1), 0)
358 + NVL(ceil(x_perf_review_period_frequency + 1), 0)
359 + NVL(ceil(x_period_of_service_id + 1), 0)
360 + NVL(ceil(x_probation_period + 1), 0)
361 + NVL(ceil(x_probation_unit + 1), 0)
362 + NVL(ceil(x_sal_review_period + 1), 0)
363 + NVL(ceil(x_sal_review_period_frequency + 1), 0)
364 + NVL(ceil(x_set_of_books_id + 1), 0)
365 + NVL(ceil(x_source_type + 1), 0)
366 + NVL(ceil(x_time_normal_finish + 1), 0)
367 + NVL(ceil(x_time_normal_start + 1), 0)
368 + NVL(ceil(x_asg_request_id + 1), 0)
369 + NVL(ceil(x_program_application_id + 1), 0)
370 + NVL(ceil(x_program_id + 1), 0)
374 + NVL(ceil(x_bargaining_unit_code + 1), 0)
371 + NVL(ceil(x_program_update_date + 1), 0)
372 + NVL(ceil(x_asg_title + 1), 0)
373 + NVL(ceil(x_object_version_number + 1), 0)
375 + NVL(ceil(x_labour_union_member_flag + 1), 0)
376 + NVL(ceil(x_hourly_salaried_code + 1), 0)
377 + NVL(ceil(x_contract_id + 1), 0)
378 + NVL(ceil(x_collective_agreement_id + 1), 0)
379 + NVL(ceil(x_cagr_id_flex_num + 1), 0)
380 + NVL(ceil(x_cagr_grade_def_id + 1), 0)
381 + NVL(ceil(x_establishment_id + 1), 0) ;
382
383
384 p_avg_row_length := x_total_wac;
385
386 END estimate_row_length;
387
388 END hr_edw_wrk_actvty_f_sizing;