[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_JOBS_M_SIZING
Source
1 PACKAGE BODY edw_hr_jobs_m_sizing AS
2 /* $Header: hriezjob.pkb 120.1 2005/06/08 02:45:51 anmajumd noship $ */
3
4 /******************************************************************************/
5 /* Sets p_row_count to the number of rows which would be collected between */
6 /* the 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 /* Cursor description */
14 CURSOR row_count_cur IS
15 SELECT count(job.job_id) total
16 FROM per_jobs job
17 WHERE NVL(job.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
18 BETWEEN p_from_date AND p_to_date;
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 /* 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_date NUMBER :=7;
38
39 x_job_total NUMBER;
40
41 /* Job Band Level */
42
43 x_job_pk NUMBER := 0;
44 x_instance NUMBER := 0;
45 x_job_name NUMBER := 0;
46 x_name NUMBER := 0;
47 x_job_dp NUMBER := 0;
48 x_business_group NUMBER := 0;
49 x_job_id NUMBER := 0;
50 x_business_group_id NUMBER := 0;
51 x_job_definition_id NUMBER := 0;
52 x_job_cat_set1 NUMBER := 0;
53 x_job_cat_set2 NUMBER := 0;
54 x_job_cat_set3 NUMBER := 0;
55 x_job_cat_set4 NUMBER := 0;
56 x_job_cat_set5 NUMBER := 0;
57 x_job_cat_set6 NUMBER := 0;
58 x_job_cat_set7 NUMBER := 0;
59 x_job_cat_set8 NUMBER := 0;
60 x_job_cat_set9 NUMBER := 0;
61 x_job_cat_set10 NUMBER := 0;
62 x_job_cat_set11 NUMBER := 0;
63 x_job_cat_set12 NUMBER := 0;
64 x_job_cat_set13 NUMBER := 0;
65 x_job_cat_set14 NUMBER := 0;
66 x_job_cat_set15 NUMBER := 0;
67 x_benchmark_job_name NUMBER := 0;
68 x_benchmark_job_id NUMBER := 0;
69 x_emp_rights_flag NUMBER := 0;
70 x_benchmark_job_flag NUMBER := 0;
71 x_creation_date NUMBER := x_date;
72 x_last_update_date NUMBER := x_date;
73
74
75 /* Select the length of the instance code */
76 CURSOR inst_cur IS
77 SELECT avg(nvl( vsize(instance_code),0 ))
78 FROM edw_local_instance;
79
80 CURSOR job_cur IS
81 SELECT
82 avg(nvl(vsize(job.job_id),0))
83 ,avg(nvl(vsize(job.name),0))
84 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,1)),0))
85 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,2)),0))
86 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,3)),0))
87 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,4)),0))
88 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,5)),0))
89 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,6)),0))
90 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,7)),0))
91 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,8)),0))
92 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,9)),0))
93 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,10)),0))
94 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,11)),0))
95 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,12)),0))
96 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,13)),0))
97 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,14)),0))
98 ,avg(nvl(vsize(hri_edw_dim_job.find_job_category(job.job_id,15)),0))
99 ,avg(nvl(vsize(job.emp_rights_flag),0))
100 ,avg(nvl(vsize(job.benchmark_job_flag),0))
101 FROM per_jobs job
102 WHERE job.last_update_date BETWEEN p_from_date AND p_to_date;
103
104 CURSOR jdef_cur IS
105 SELECT avg(nvl(vsize(jdef.job_definition_id),0))
106 FROM per_job_definitions jdef, per_jobs job
107 WHERE job.last_update_date BETWEEN p_from_date AND p_to_date;
108
109 CURSOR bgr_cur IS
110 SELECT
111 avg(nvl(vsize(organization_id),0))
112 ,avg(nvl(vsize(bgr.name),0))
113 FROM hr_all_organization_units bgr, per_jobs job
114 WHERE job.last_update_date BETWEEN p_from_date AND p_to_date;
115
116
117 BEGIN
118
119 OPEN inst_cur;
120 FETCH inst_cur INTO x_instance;
121 CLOSE inst_cur;
122
123 OPEN job_cur;
124 FETCH job_cur INTO
125 x_job_id
126 ,x_job_name
127 ,x_job_cat_set1
128 ,x_job_cat_set2
129 ,x_job_cat_set3
130 ,x_job_cat_set4
131 ,x_job_cat_set5
132 ,x_job_cat_set6
133 ,x_job_cat_set7
134 ,x_job_cat_set8
135 ,x_job_cat_set9
136 ,x_job_cat_set10
137 ,x_job_cat_set11
138 ,x_job_cat_set12
139 ,x_job_cat_set13
140 ,x_job_cat_set14
141 ,x_job_cat_set15
142 ,x_emp_rights_flag
143 ,x_benchmark_job_flag;
144 CLOSE job_cur;
145
146 OPEN jdef_cur;
147 FETCH jdef_cur INTO
148 x_job_definition_id;
149 CLOSE jdef_cur;
150
151 OPEN bgr_cur;
152 FETCH bgr_cur INTO x_business_group, x_business_group_id;
153 CLOSE bgr_cur;
154
155 x_benchmark_job_name := x_job_name;
156 x_benchmark_job_id := x_job_id;
157 x_job_pk := x_job_id + x_instance;
158 x_name := x_job_name + x_business_group + x_instance;
159 x_job_dp := x_name;
160
161 x_job_total := NVL(ceil(x_job_pk + 1), 0 )
162 + NVL(ceil(x_instance + 1), 0 )
163 + NVL(ceil(x_name + 1), 0 )
164 + NVL(ceil(x_job_dp + 1), 0 )
165 + NVL(ceil(x_business_group + 1), 0 )
166 + NVL(ceil(x_job_id + 1), 0 )
167 + NVL(ceil(x_business_group_id + 1), 0 )
168 + NVL(ceil(x_job_definition_id + 1), 0 )
169 + NVL(ceil(x_job_cat_set1 + 1), 0 )
170 + NVL(ceil(x_job_cat_set2 + 1), 0 )
171 + NVL(ceil(x_job_cat_set3 + 1), 0 )
172 + NVL(ceil(x_job_cat_set4 + 1), 0 )
173 + NVL(ceil(x_job_cat_set5 + 1), 0 )
174 + NVL(ceil(x_job_cat_set6 + 1), 0 )
175 + NVL(ceil(x_job_cat_set7 + 1), 0 )
176 + NVL(ceil(x_job_cat_set8 + 1), 0 )
177 + NVL(ceil(x_job_cat_set9 + 1), 0 )
178 + NVL(ceil(x_job_cat_set1 + 1), 0 )
179 + NVL(ceil(x_job_cat_set11 + 1), 0 )
180 + NVL(ceil(x_job_cat_set12 + 1), 0 )
181 + NVL(ceil(x_job_cat_set13 + 1), 0 )
182 + NVL(ceil(x_job_cat_set14 + 1), 0 )
183 + NVL(ceil(x_job_cat_set15 + 1), 0 )
184 + NVL(ceil(x_benchmark_job_name + 1), 0 )
185 + NVL(ceil(x_benchmark_job_id + 1), 0 )
186 + NVL(ceil(x_emp_rights_flag + 1), 0 )
187 + NVL(ceil(x_benchmark_job_flag + 1), 0 )
188 + NVL(ceil(x_creation_date + 1), 0 )
189 + NVL(ceil(x_last_update_date + 1), 0 );
190
191 p_avg_row_length := x_job_total;
192
193 END estimate_row_length;
194
195 END edw_hr_jobs_m_sizing;