[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_MVMNT_TYP_M_SIZING
Source
1 PACKAGE BODY edw_hr_mvmnt_typ_m_sizing AS
2 /* $Header: hriezmvt.pkb 120.1 2005/06/08 02:47:02 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(combination_id) total
15 FROM hri_edw_event_hrchy_cmbns
16 WHERE NVL(last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
17 BETWEEN p_from_date AND p_to_date;
18
19
20 BEGIN
21
22 OPEN row_count_cur;
23 FETCH row_count_cur INTO p_row_count;
24 CLOSE row_count_cur;
25
26 END count_source_rows;
27
28
29 /******************************************************************************/
30 /* Estimates row lengths. */
31 /******************************************************************************/
32 PROCEDURE estimate_row_length( p_from_date IN DATE,
33 p_to_date IN DATE,
34 p_avg_row_length OUT NOCOPY NUMBER )
35
36 IS
37
38 /***************************/
39 /* DECLARE LOCAL VARIABLES */
40 /***************************/
41
42 x_date NUMBER :=7;
43
44 x_total_mvt NUMBER;
45 x_total_gain3 NUMBER;
46 x_total_gain2 NUMBER;
47 x_total_gain1 NUMBER;
48 x_total_rec3 NUMBER;
49 x_total_rec2 NUMBER;
50 x_total_rec1 NUMBER;
51 x_total_sep3 NUMBER;
52 x_total_sep2 NUMBER;
53 x_total_sep1 NUMBER;
54 x_total_loss3 NUMBER;
55 x_total_loss2 NUMBER;
56 x_total_loss1 NUMBER;
57
58 /* Movement Level */
59
60 x_movement_pk NUMBER :=0;
61 x_instance NUMBER :=0;
62 x_name NUMBER :=0;
63 x_movement_dp NUMBER :=0;
64 x_movement_cmbn_id NUMBER :=0;
65 x_last_update_date NUMBER :=x_date;
66 x_creation_date NUMBER :=x_date;
67
68 /* GAINS */
69
70 /* Gain Level 1 */
71
72 x_gain_lvl1_pk NUMBER :=0;
73 x_gain_lvl1_dp NUMBER :=0;
74 x_gain_lvl1_code NUMBER :=0;
75 x_gain_lvl1_name NUMBER :=0;
76 x_gain_lvl1_id NUMBER :=0;
77
78 /* Gain Level 2 */
79
80 x_gain_lvl2_pk NUMBER :=0;
81 x_gain_lvl2_dp NUMBER :=0;
82 x_gain_lvl2_id NUMBER :=0;
83 x_gain_lvl2_code NUMBER :=0;
84 x_gain_lvl2_name NUMBER :=0;
85
86 /* Gain Level 3 */
87
88 x_gain_lvl3_pk NUMBER :=0;
89 x_gain_lvl3_dp NUMBER :=0;
90 x_gain_lvl3_id NUMBER :=0;
91 x_gain_lvl3_code NUMBER :=0;
92 x_gain_lvl3_name NUMBER :=0;
93
94 /* LOSSES */
95
96 /* Loss Level 1 */
97
98 x_loss_lvl1_pk NUMBER :=0;
99 x_loss_lvl1_dp NUMBER :=0;
100 x_loss_lvl1_id NUMBER :=0;
101 x_loss_lvl1_code NUMBER :=0;
102 x_loss_lvl1_name NUMBER :=0;
103
104 /* Loss Level 2 */
105
106 x_loss_lvl2_pk NUMBER :=0;
107 x_loss_lvl2_dp NUMBER :=0;
108 x_loss_lvl2_id NUMBER :=0;
109 x_loss_lvl2_code NUMBER :=0;
110 x_loss_lvl2_name NUMBER :=0;
111
112 /* Loss Level 3 */
113
114 x_loss_lvl3_pk NUMBER :=0;
115 x_loss_lvl3_dp NUMBER :=0;
116 x_loss_lvl3_id NUMBER :=0;
117 x_loss_lvl3_code NUMBER :=0;
118 x_loss_lvl3_name NUMBER :=0;
119
120 /* RECRUITMENT */
121
122 /* Recruitment Level 1 */
123
124 x_rec_lvl1_pk NUMBER :=0;
125 x_rec_lvl1_dp NUMBER :=0;
126 x_rec_lvl1_id NUMBER :=0;
127 x_rec_lvl1_code NUMBER :=0;
128 x_rec_lvl1_name NUMBER :=0;
129
130 /* Recruitment Level 2 */
131
132 x_rec_lvl2_pk NUMBER :=0;
133 x_rec_lvl2_dp NUMBER :=0;
134 x_rec_lvl2_id NUMBER :=0;
135 x_rec_lvl2_code NUMBER :=0;
136 x_rec_lvl2_name NUMBER :=0;
137
138 /* Recruitment Level 3 */
139
140 x_rec_lvl3_pk NUMBER :=0;
141 x_rec_lvl3_dp NUMBER :=0;
142 x_rec_lvl3_id NUMBER :=0;
143 x_rec_lvl3_code NUMBER :=0;
144 x_rec_lvl3_name NUMBER :=0;
145
146 /* SEPARATION */
147
148 /* Seperation Level 1 */
149
150 x_sep_lvl1_pk NUMBER :=0;
151 x_sep_lvl1_dp NUMBER :=0;
152 x_sep_lvl1_id NUMBER :=0;
153 x_sep_lvl1_code NUMBER :=0;
154 x_sep_lvl1_name NUMBER :=0;
155
156 /* Seperation Level 2 */
157
158 x_sep_lvl2_pk NUMBER :=0;
159 x_sep_lvl2_dp NUMBER :=0;
160 x_sep_lvl2_id NUMBER :=0;
161 x_sep_lvl2_code NUMBER :=0;
162 x_sep_lvl2_name NUMBER :=0;
163
164 /* Seperation Level 3 */
165
166 x_sep_lvl3_pk NUMBER :=0;
167 x_sep_lvl3_dp NUMBER :=0;
168 x_sep_lvl3_id NUMBER :=0;
169 x_sep_lvl3_code NUMBER :=0;
170 x_sep_lvl3_name NUMBER :=0;
171
172 /*******************/
173 /* DECLARE CURSORS */
174 /*******************/
175
176 /* Select the length of the instance code */
177 CURSOR inst_cur IS
178 SELECT avg(nvl( vsize(instance_code),0 ))
179 FROM edw_local_instance;
180
181
182 CURSOR mvt_cur IS
183 SELECT
184 avg(nvl(vsize(cmbn.description),0))
185 ,avg(nvl(vsize(cmbn.combination_id),0))
186 FROM
187 hri_edw_event_hrchy_cmbns cmbn
188 WHERE cmbn.last_update_date BETWEEN p_from_date AND p_to_date;
189
190 /* GAINS */
191
192 CURSOR gain1_cur IS
193 SELECT
194 avg(nvl(vsize(gns.event_code),0))
195 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_GAIN_TYPES',gns.event_code)),0))
196 ,avg(nvl(vsize(gns.event_id),0))
197 FROM
198 hri_edw_event_hrchys gns
199 WHERE gns.level_number = 1
200 AND gns.hierarchy = 'Gain'
201 AND gns.last_update_date BETWEEN p_from_date AND p_to_date;
202
203 CURSOR gain2_cur IS
204 SELECT
205 avg(nvl(vsize(gns.event_code),0))
206 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_GAIN_TYPES',gns.event_code)),0))
207 ,avg(nvl(vsize(gns.event_id),0))
208 FROM
209 hri_edw_event_hrchys gns
210 WHERE gns.level_number = 2
211 AND gns.hierarchy = 'Gain'
212 AND gns.last_update_date BETWEEN p_from_date AND p_to_date;
213
214 CURSOR gain3_cur IS
215 SELECT
216 avg(nvl(vsize(gns.event_code),0))
217 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_GAIN_TYPES',gns.event_code)),0))
218 ,avg(nvl(vsize(gns.event_id),0))
219 FROM
220 hri_edw_event_hrchys gns
221 WHERE gns.level_number = 3
222 AND gns.hierarchy = 'Gain'
223 AND gns.last_update_date BETWEEN p_from_date AND p_to_date;
224
225 /* LOSSES */
226
227 CURSOR loss1_cur IS
228 SELECT
229 avg(nvl(vsize(lss.event_code),0))
230 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_LOSS_TYPES',lss.event_code)),0))
231 ,avg(nvl(vsize(lss.event_id),0))
232 FROM
233 hri_edw_event_hrchys lss
234 WHERE lss.level_number = 1
235 AND lss.hierarchy = 'Loss'
236 AND lss.last_update_date BETWEEN p_from_date AND p_to_date;
237
238 CURSOR loss2_cur IS
239 SELECT
240 avg(nvl(vsize(lss.event_code),0))
241 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_LOSS_TYPES',lss.event_code)),0))
242 ,avg(nvl(vsize(lss.event_id),0))
243 FROM
244 hri_edw_event_hrchys lss
245 WHERE lss.level_number = 2
246 AND lss.hierarchy = 'Loss'
247 AND lss.last_update_date BETWEEN p_from_date AND p_to_date;
248
249 CURSOR loss3_cur IS
250 SELECT
251 avg(nvl(vsize(lss.event_code),0))
252 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_LOSS_TYPES',lss.event_code)),0))
253 ,avg(nvl(vsize(lss.event_id),0))
254 FROM
255 hri_edw_event_hrchys lss
256 WHERE lss.level_number = 3
257 AND lss.hierarchy = 'Loss'
258 AND lss.last_update_date BETWEEN p_from_date AND p_to_date;
259
260 /* RECRUITMENT */
261
262 CURSOR rec1_cur IS
263 SELECT
264 avg(nvl(vsize(rec.event_code),0))
265 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_RECRUITMENT_STAGES',rec.event_code)),0))
266 ,avg(nvl(vsize(rec.event_id),0))
267 FROM
268 hri_edw_event_hrchys rec
269 WHERE rec.level_number = 1
270 AND rec.hierarchy = 'Recruitment'
271 AND rec.last_update_date BETWEEN p_from_date AND p_to_date;
272
273 CURSOR rec2_cur IS
274 SELECT
275 avg(nvl(vsize(rec.event_code),0))
276 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_RECRUITMENT_STAGES',rec.event_code)),0))
277 ,avg(nvl(vsize(rec.event_id),0))
278 FROM
279 hri_edw_event_hrchys rec
280 WHERE rec.level_number = 2
281 AND rec.hierarchy = 'Recruitment'
282 AND rec.last_update_date BETWEEN p_from_date AND p_to_date;
283
284 CURSOR rec3_cur IS
285 SELECT
286 avg(nvl(vsize(rec.event_code),0))
287 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_RECRUITMENT_STAGES',rec.event_code)),0))
288 ,avg(nvl(vsize(rec.event_id),0))
289 FROM
290 hri_edw_event_hrchys rec
291 WHERE rec.level_number = 3
292 AND rec.hierarchy = 'Recruitment'
293 AND rec.last_update_date BETWEEN p_from_date AND p_to_date;
294
295 /* SEPARATION */
296
297 CURSOR spn1_cur IS
298 SELECT
299 avg(nvl(vsize(spn.event_code),0))
300 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_SEPARATION_STAGES',spn.event_code)),0))
301 ,avg(nvl(vsize(spn.event_id),0))
302 FROM
303 hri_edw_event_hrchys spn
304 WHERE spn.level_number = 1
305 AND spn.hierarchy = 'Separations'
306 AND spn.last_update_date BETWEEN p_from_date AND p_to_date;
307
308 CURSOR spn2_cur IS
309 SELECT
310 avg(nvl(vsize(spn.event_code),0))
311 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_SEPARATION_STAGES',spn.event_code)),0))
312 ,avg(nvl(vsize(spn.event_id),0))
313 FROM
314 hri_edw_event_hrchys spn
315 WHERE spn.level_number = 2
316 AND spn.hierarchy = 'Separations'
317 AND spn.last_update_date BETWEEN p_from_date AND p_to_date;
318
319 CURSOR spn3_cur IS
320 SELECT
321 avg(nvl(vsize(spn.event_code),0))
322 ,avg(nvl(vsize(hr_general.decode_lookup('HRI_SEPARATION_STAGES',spn.event_code)),0))
323 ,avg(nvl(vsize(spn.event_id),0))
324 FROM
325 hri_edw_event_hrchys spn
326 WHERE spn.level_number = 3
327 AND spn.hierarchy = 'Separations'
328 AND spn.last_update_date BETWEEN p_from_date AND p_to_date;
329
330 /******************************************************************************/
331
332 BEGIN
333
334 OPEN inst_cur;
335 FETCH inst_cur INTO x_instance;
336 CLOSE inst_cur;
337
338
339 /* GAINS */
340
341 OPEN gain3_cur;
342 FETCH gain3_cur INTO
343 x_gain_lvl3_code
344 ,x_gain_lvl3_dp
345 ,x_gain_lvl3_id;
346 CLOSE gain3_cur;
347
348 x_gain_lvl3_pk := x_gain_lvl3_code + x_instance;
349 x_gain_lvl3_name := x_gain_lvl3_dp;
350
351 x_total_gain3 := NVL(ceil(x_gain_lvl3_pk + 1), 0)
352 + NVL(ceil(x_instance + 1), 0)
353 + NVL(ceil(x_gain_lvl3_dp + 1), 0)
354 + NVL(ceil(x_gain_lvl3_code + 1), 0)
355 + NVL(ceil(x_gain_lvl3_id + 1), 0)
356 + NVL(ceil(x_last_update_date + 1), 0)
357 + NVL(ceil(x_creation_date + 1), 0);
358
359 OPEN gain2_cur;
360 FETCH gain2_cur INTO
361 x_gain_lvl2_code
362 ,x_gain_lvl2_dp
363 ,x_gain_lvl2_id;
364 CLOSE gain2_cur;
365
366 x_gain_lvl2_pk := x_gain_lvl3_pk + x_gain_lvl2_code + x_instance;
367 x_gain_lvl2_name := x_gain_lvl2_dp;
368
369 x_total_gain2 := NVL(ceil(x_gain_lvl2_pk + 1), 0)
370 + NVL(ceil(x_gain_lvl3_pk + 1), 0)
371 + NVL(ceil(x_instance + 1), 0)
372 + NVL(ceil(x_gain_lvl2_dp + 1), 0)
373 + NVL(ceil(x_gain_lvl2_name + 1), 0)
374 + NVL(ceil(x_gain_lvl2_code + 1), 0)
375 + NVL(ceil(x_gain_lvl2_id + 1), 0)
376 + NVL(ceil(x_last_update_date + 1), 0)
377 + NVL(ceil(x_creation_date + 1), 0);
378
379 OPEN gain1_cur;
380 FETCH gain1_cur INTO
381 x_gain_lvl1_code
382 ,x_gain_lvl1_dp
383 ,x_gain_lvl1_id;
384 CLOSE gain1_cur;
385
386 x_gain_lvl1_pk := x_gain_lvl2_pk + x_gain_lvl1_code + x_instance;
387 x_gain_lvl1_name := x_gain_lvl1_dp;
388
389 x_total_gain1 := NVL(ceil(x_gain_lvl1_pk + 1), 0)
390 + NVL(ceil(x_gain_lvl2_pk + 1), 0)
391 + NVL(ceil(x_instance + 1), 0)
392 + NVL(ceil(x_gain_lvl1_dp + 1), 0)
393 + NVL(ceil(x_gain_lvl1_name + 1), 0)
394 + NVL(ceil(x_gain_lvl1_code + 1), 0)
395 + NVL(ceil(x_gain_lvl1_id + 1), 0)
396 + NVL(ceil(x_last_update_date + 1), 0)
400
397 + NVL(ceil(x_creation_date + 1), 0);
398
399 /* LOSSES */
401 OPEN loss3_cur;
402 FETCH loss3_cur INTO
403 x_loss_lvl3_code
404 ,x_loss_lvl3_dp
405 ,x_loss_lvl3_id;
406 CLOSE loss3_cur;
407
408 x_loss_lvl3_pk := x_loss_lvl3_code + x_instance;
409 x_loss_lvl3_name := x_loss_lvl3_dp;
410
411 x_total_loss3 := NVL(ceil(x_loss_lvl3_pk + 1), 0)
412 + NVL(ceil(x_instance + 1), 0)
413 + NVL(ceil(x_loss_lvl3_name + 1), 0)
414 + NVL(ceil(x_loss_lvl3_dp + 1), 0)
415 + NVL(ceil(x_loss_lvl3_code + 1), 0)
416 + NVL(ceil(x_loss_lvl3_id + 1), 0)
417 + NVL(ceil(x_last_update_date + 1), 0)
418 + NVL(ceil(x_creation_date + 1), 0);
419
420 OPEN loss2_cur;
421 FETCH loss2_cur INTO
422 x_loss_lvl2_code
423 ,x_loss_lvl2_dp
424 ,x_loss_lvl2_id;
425 CLOSE loss2_cur;
426
427 x_loss_lvl2_pk := x_loss_lvl2_code + x_instance;
428 x_loss_lvl2_name := x_loss_lvl2_dp;
429
430 x_total_loss2 := NVL(ceil(x_loss_lvl2_pk + 1), 0)
431 + NVL(ceil(x_loss_lvl3_pk + 1), 0)
432 + NVL(ceil(x_instance + 1), 0)
433 + NVL(ceil(x_loss_lvl2_name + 1), 0)
434 + NVL(ceil(x_loss_lvl2_dp + 1), 0)
435 + NVL(ceil(x_loss_lvl2_code + 1), 0)
436 + NVL(ceil(x_loss_lvl2_id + 1), 0)
437 + NVL(ceil(x_last_update_date + 1), 0)
438 + NVL(ceil(x_creation_date + 1), 0);
439
440 OPEN loss1_cur;
441 FETCH loss1_cur INTO
442 x_loss_lvl1_code
443 ,x_loss_lvl1_dp
444 ,x_loss_lvl1_id;
445 CLOSE loss1_cur;
446
447 x_loss_lvl1_pk := x_loss_lvl1_code + x_instance;
448 x_loss_lvl1_name := x_loss_lvl1_dp;
449
450 x_total_loss1 := NVL(ceil(x_loss_lvl1_pk + 1), 0)
451 + NVL(ceil(x_loss_lvl2_pk + 1), 0)
452 + NVL(ceil(x_instance + 1), 0)
453 + NVL(ceil(x_loss_lvl1_name + 1), 0)
454 + NVL(ceil(x_loss_lvl1_dp + 1), 0)
455 + NVL(ceil(x_loss_lvl1_code + 1), 0)
456 + NVL(ceil(x_loss_lvl1_id + 1), 0)
457 + NVL(ceil(x_last_update_date + 1), 0)
458 + NVL(ceil(x_creation_date + 1), 0);
459
460 /* RECRUITMENT */
461
462 OPEN rec3_cur;
463 FETCH rec3_cur INTO
464 x_rec_lvl3_code
465 ,x_rec_lvl3_dp
466 ,x_rec_lvl3_id;
467 CLOSE rec3_cur;
468
469 x_rec_lvl3_pk := x_rec_lvl3_code + x_instance;
470 x_rec_lvl3_name := x_rec_lvl3_dp;
471
472 x_total_rec3 := NVL(ceil(x_rec_lvl3_pk + 1), 0)
473 + NVL(ceil(x_instance + 1), 0)
474 + NVL(ceil(x_rec_lvl3_name + 1), 0)
475 + NVL(ceil(x_rec_lvl3_dp + 1), 0)
476 + NVL(ceil(x_rec_lvl3_code + 1), 0)
477 + NVL(ceil(x_rec_lvl3_id + 1), 0)
478 + NVL(ceil(x_last_update_date + 1), 0)
479 + NVL(ceil(x_creation_date + 1), 0);
480
481 OPEN rec2_cur;
482 FETCH rec2_cur INTO
483 x_rec_lvl2_code
484 ,x_rec_lvl2_dp
485 ,x_rec_lvl2_id;
486 CLOSE rec2_cur;
487
488 x_rec_lvl2_pk := x_rec_lvl2_code + x_instance;
489 x_rec_lvl2_name := x_rec_lvl2_dp;
490
491 x_total_rec2 := NVL(ceil(x_rec_lvl2_pk + 1), 0)
492 + NVL(ceil(x_rec_lvl3_pk + 1), 0)
493 + NVL(ceil(x_instance + 1), 0)
494 + NVL(ceil(x_rec_lvl2_name + 1), 0)
495 + NVL(ceil(x_rec_lvl2_dp + 1), 0)
496 + NVL(ceil(x_rec_lvl2_code + 1), 0)
497 + NVL(ceil(x_rec_lvl2_id + 1), 0)
498 + NVL(ceil(x_last_update_date + 1), 0)
499 + NVL(ceil(x_creation_date + 1), 0);
500
501 OPEN rec1_cur;
502 FETCH rec1_cur INTO
503 x_rec_lvl1_code
504 ,x_rec_lvl1_dp
505 ,x_rec_lvl1_id;
506 CLOSE rec1_cur;
507
508 x_rec_lvl1_pk := x_rec_lvl1_code + x_instance;
509 x_rec_lvl1_name := x_rec_lvl1_dp;
510
511 x_total_rec1 := NVL(ceil(x_rec_lvl1_pk + 1), 0)
512 + NVL(ceil(x_rec_lvl2_pk + 1), 0)
513 + NVL(ceil(x_instance + 1), 0)
514 + NVL(ceil(x_rec_lvl1_name + 1), 0)
515 + NVL(ceil(x_rec_lvl1_dp + 1), 0)
516 + NVL(ceil(x_rec_lvl1_code + 1), 0)
517 + NVL(ceil(x_rec_lvl1_id + 1), 0)
518 + NVL(ceil(x_last_update_date + 1), 0)
519 + NVL(ceil(x_creation_date + 1), 0);
520
521 /* SEPARATION */
522
523 OPEN spn3_cur;
524 FETCH spn3_cur INTO
525 x_sep_lvl3_code
526 ,x_sep_lvl3_dp
527 ,x_sep_lvl3_id;
528 CLOSE spn3_cur;
529
530 x_sep_lvl3_pk := x_sep_lvl3_code + x_instance;
531 x_sep_lvl3_name := x_sep_lvl3_dp;
532
533 x_total_sep3 := NVL(ceil(x_sep_lvl3_pk + 1), 0)
534 + NVL(ceil(x_instance + 1), 0)
535 + NVL(ceil(x_sep_lvl3_name + 1), 0)
536 + NVL(ceil(x_sep_lvl3_dp + 1), 0)
540 + NVL(ceil(x_creation_date + 1), 0);
537 + NVL(ceil(x_sep_lvl3_code + 1), 0)
538 + NVL(ceil(x_sep_lvl3_id + 1), 0)
539 + NVL(ceil(x_last_update_date + 1), 0)
541
542 OPEN spn2_cur;
543 FETCH spn2_cur INTO
544 x_sep_lvl2_code
545 ,x_sep_lvl2_dp
546 ,x_sep_lvl2_id;
547 CLOSE spn2_cur;
548
549 x_sep_lvl2_pk := x_sep_lvl2_code + x_instance;
550 x_sep_lvl2_name := x_sep_lvl2_dp;
551
552 x_total_sep2 := NVL(ceil(x_sep_lvl2_pk + 1), 0)
553 + NVL(ceil(x_sep_lvl3_pk + 1), 0)
554 + NVL(ceil(x_instance + 1), 0)
555 + NVL(ceil(x_sep_lvl2_name + 1), 0)
556 + NVL(ceil(x_sep_lvl2_dp + 1), 0)
557 + NVL(ceil(x_sep_lvl2_code + 1), 0)
558 + NVL(ceil(x_sep_lvl2_id + 1), 0)
559 + NVL(ceil(x_last_update_date + 1), 0)
560 + NVL(ceil(x_creation_date + 1), 0);
561
562 OPEN spn1_cur;
563 FETCH spn1_cur INTO
564 x_sep_lvl1_code
565 ,x_sep_lvl1_dp
566 ,x_sep_lvl1_id;
567 CLOSE spn1_cur;
568
569 x_sep_lvl1_pk := x_sep_lvl1_code + x_instance;
570 x_sep_lvl1_name := x_sep_lvl1_dp;
571
572 x_total_sep1 := NVL(ceil(x_sep_lvl1_pk + 1), 0)
573 + NVL(ceil(x_sep_lvl2_pk + 1), 0)
574 + NVL(ceil(x_instance + 1), 0)
575 + NVL(ceil(x_sep_lvl1_name + 1), 0)
576 + NVL(ceil(x_sep_lvl1_dp + 1), 0)
577 + NVL(ceil(x_sep_lvl1_code + 1), 0)
578 + NVL(ceil(x_sep_lvl1_id + 1), 0)
579 + NVL(ceil(x_last_update_date + 1), 0)
580 + NVL(ceil(x_creation_date + 1), 0);
581
582 /* MOVEMENTS */
583
584 OPEN mvt_cur;
585 FETCH mvt_cur INTO
586 x_name
587 ,x_movement_cmbn_id;
588 CLOSE mvt_cur;
589
590 x_movement_pk := x_gain_lvl1_pk
591 + x_loss_lvl1_pk
592 + x_rec_lvl1_pk
593 + x_sep_lvl1_pk
594 + x_instance ;
595
596 x_movement_dp := x_name;
597
598 x_total_mvt := NVL(ceil(x_movement_pk + 1), 0)
599 + NVL(ceil(x_gain_lvl1_pk + 1), 0)
600 + NVL(ceil(x_loss_lvl1_pk + 1), 0)
601 + NVL(ceil(x_rec_lvl1_pk + 1), 0)
602 + NVL(ceil(x_sep_lvl1_pk + 1), 0)
603 + NVL(ceil(x_instance + 1), 0)
604 + NVL(ceil(x_name + 1), 0)
605 + NVL(ceil(x_movement_dp + 1), 0)
606 + NVL(ceil(x_movement_cmbn_id + 1), 0)
607 + NVL(ceil(x_last_update_date + 1), 0)
608 + NVL(ceil(x_creation_date + 1), 0);
609
610 /* TOTAL */
611
612 p_avg_row_length := x_total_mvt +
613 x_total_gain3 +
614 x_total_gain2 +
615 x_total_gain1 +
616 x_total_rec3 +
617 x_total_rec2 +
618 x_total_rec1 +
619 x_total_sep3 +
620 x_total_sep2 +
621 x_total_sep1 +
622 x_total_loss3 +
623 x_total_loss2 +
624 x_total_loss1;
625
626 END estimate_row_length;
627
628 END edw_hr_mvmnt_typ_m_sizing;