[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_ASG_CHNG_M_SIZING
Source
1 PACKAGE BODY edw_hr_asg_chng_m_sizing AS
2 /* $Header: hriezacg.pkb 120.1 2005/06/08 02:44: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(*) total
15 FROM
16 (SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
17 ,lookup_code
18 ,last_update_date
19 ,creation_date
20 FROM hr_lookups
21 WHERE lookup_type = 'YES_NO') t1
22 ,(SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
23 ,lookup_code
24 FROM hr_lookups
25 WHERE lookup_type = 'YES_NO') t2
26 ,(SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
27 ,lookup_code
28 FROM hr_lookups
29 WHERE lookup_type = 'YES_NO') t3
30 ,(SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
31 ,lookup_code
32 FROM hr_lookups
33 WHERE lookup_type = 'YES_NO') t4
34 ,(SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
35 ,lookup_code
36 FROM hr_lookups
37 WHERE lookup_type = 'YES_NO') t5
38 ,(SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
39 ,lookup_code
40 FROM hr_lookups
41 WHERE lookup_type = 'YES_NO') t6
42 ,(SELECT hr_general.decode_lookup('YES_NO', lookup_code) lookup
43 ,lookup_code
44 FROM hr_lookups
45 WHERE lookup_type = 'YES_NO') t7;
46
47 BEGIN
48
49 OPEN row_count_cur;
50 FETCH row_count_cur INTO p_row_count;
51 CLOSE row_count_cur;
52
53 END count_source_rows;
54
55
56 /******************************************************************************/
57 /* Estimates row lengths. */
58 /******************************************************************************/
59 PROCEDURE estimate_row_length( p_from_date IN DATE,
60 p_to_date IN DATE,
61 p_avg_row_length OUT NOCOPY NUMBER )
62
63 IS
64
65 x_date NUMBER :=7;
66
67 x_total_asg_chng NUMBER;
68
69 /* Assignment Change Level */
70 x_asg_chng_pk NUMBER :=0;
71 x_instance NUMBER :=0;
72 x_name NUMBER :=0;
73 x_asg_chng_dp NUMBER :=0;
74 x_flag NUMBER :=0;
75 x_yes NUMBER :=0;
76 x_no NUMBER :=0;
77 x_creation_date NUMBER := x_date;
78 x_last_update_date NUMBER := x_date;
79
80 /* Select the length of the instance code */
81 CURSOR inst_cur IS
82 SELECT avg(nvl( vsize(instance_code),0 ))
83 FROM edw_local_instance;
84
85
86 CURSOR flag_cur IS
87 SELECT
88 avg(nvl(vsize(hr_general.decode_lookup('YES_NO','Y')),0))
89 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO','N')),0))
90 FROM dual;
91
92 BEGIN
93
94 OPEN inst_cur;
95 FETCH inst_cur INTO x_instance;
96 CLOSE inst_cur;
97
98 OPEN flag_cur;
99 FETCH flag_cur INTO x_yes, x_no;
100 CLOSE flag_cur;
101
102 x_flag := (x_yes + x_no) / 2;
103
104 /* Assignment Change Level */
105
106 x_asg_chng_pk := x_instance;
107 x_name := x_instance;
108 x_asg_chng_dp := x_name;
109
110 x_total_asg_chng := NVL(ceil(x_asg_chng_pk + 1), 0)
111 + NVL(ceil(x_instance + 1), 0)
112 + NVL(ceil(x_name + 1), 0)
113 + NVL(ceil(x_asg_chng_dp + 1), 0)
114 + (7 * NVL(ceil(x_flag + 1), 0))
115 + NVL(ceil(x_last_update_date + 1), 0)
116 + NVL(ceil(x_creation_date + 1), 0);
117
118 /* TOTAL */
119
120 p_avg_row_length := x_total_asg_chng;
121
122 END estimate_row_length;
123
124 END edw_hr_asg_chng_m_sizing;