[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_ASSGNMNT_M_SIZING
Source
1 PACKAGE BODY edw_hr_assgnmnt_m_sizing AS
2 /* $Header: hriezasg.pkb 120.1 2005/06/08 02:45: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 per_all_assignments_f asg
17 /* Generates list of earliest date tracked start dates by assignment */
18 ,(select assignment_id, effective_start_date
19 from per_all_assignments_f asg
20 minus
21 /* Remove any assignment start date which has an earlier start date */
22 select assignment_id, effective_start_date
23 from per_all_assignments_f asg
24 where exists (select 1 from per_all_assignments_f asg1
25 where asg1.assignment_id = asg.assignment_id
26 and asg1.effective_start_date < asg.effective_start_date)
27 )asg_start
28 WHERE
29 asg_start.assignment_id = asg.assignment_id
30 AND asg_start.effective_start_date = asg.effective_start_date
31 AND NVL(asg.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
32 BETWEEN p_from_date AND p_to_date;
33
34 BEGIN
35
36 OPEN row_count_cur;
37 FETCH row_count_cur INTO p_row_count;
38 CLOSE row_count_cur;
39
40 END count_source_rows;
41
42
43 /******************************************************************************/
44 /* Estimates row lengths. */
45 /******************************************************************************/
46 PROCEDURE estimate_row_length( p_from_date IN DATE,
47 p_to_date IN DATE,
48 p_avg_row_length OUT NOCOPY NUMBER )
49
50 IS
51
52 x_date NUMBER :=7;
53
54 x_total_assgnmnt NUMBER;
55
56 /* Assignment Level */
57
58 x_assignment_pk NUMBER :=0;
59 x_name NUMBER :=0;
60 x_assignment_dp NUMBER :=0;
61 x_business_group NUMBER :=0;
62 x_assignment_number NUMBER :=0;
63 x_start_date NUMBER :=x_date;
64 x_end_date NUMBER :=x_date;
65 x_primary_flag NUMBER :=0;
66 x_instance NUMBER :=0;
67 x_assignment_id NUMBER :=0;
68 x_assignment_status_type_id NUMBER :=0;
69 x_business_group_id NUMBER :=0;
70 x_people_group_id NUMBER :=0;
71 x_effective_start_date NUMBER :=x_date;
72 x_effective_end_date NUMBER :=x_date;
73 x_title NUMBER :=0;
74 x_normal_hours_frequency NUMBER :=0;
75 x_normal_hours NUMBER :=0;
76 x_assignment_status NUMBER :=0;
77 x_assignment_type NUMBER :=0;
78 x_manager_flag NUMBER :=0;
79 x_time_normal_start NUMBER :=0;
80 x_time_normal_end NUMBER :=0;
81 x_probation_period_unit NUMBER :=0;
82 x_probation_period NUMBER :=0;
83 x_perf_review_period_frqncy NUMBER :=0;
84 x_performance_review_period NUMBER :=0;
85 x_slry_review_period_frqncy NUMBER :=0;
86 x_slry_review_period NUMBER :=0;
87 x_hourly_salaried_flag NUMBER :=0;
88 x_last_update_date NUMBER := x_date;
89 x_creation_date NUMBER := x_date;
90
91
92
93 /* Select the length of the instance code */
94 CURSOR inst_cur IS
95 SELECT avg(nvl( vsize(instance_code),0 ))
96 FROM edw_local_instance;
97
98 CURSOR asg_cur IS
99 SELECT
100 avg(nvl(vsize(assignment_number),0))
101 ,avg(nvl(vsize(assignment_id),0))
102 ,avg(nvl(vsize(title),0))
103 ,avg(nvl(vsize(normal_hours),0))
104 ,avg(nvl(vsize(time_normal_start),0))
105 ,avg(nvl(vsize(time_normal_finish),0))
106 ,avg(nvl(vsize(probation_period),0))
107 ,avg(nvl(vsize(perf_review_period),0))
108 ,avg(nvl(vsize(sal_review_period),0))
109 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO', primary_flag)),0))
110 ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', frequency)),0))
111 ,avg(nvl(vsize(hr_general.decode_lookup('EMP_APL', assignment_type)),0))
112 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO', manager_flag)),0))
113 ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', probation_unit)),0))
114 ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', perf_review_period_frequency)),0))
115 ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY', sal_review_period_frequency)),0))
116 ,avg(nvl(vsize(hr_general.decode_lookup('HOURLY_SALARIED_CODE', hourly_salaried_code)),0))
117 FROM per_all_assignments_f
118 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
119
120 CURSOR ast_cur IS
121 SELECT
122 avg(nvl(vsize(assignment_status_type_id),0))
123 FROM per_assignment_status_types
124 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
125
126 CURSOR pgr_cur IS
127 SELECT avg(nvl(vsize(people_group_id),0))
128 FROM pay_people_groups
129 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
130
131 CURSOR bgr_cur IS
132 SELECT avg(nvl(vsize(name),0))
133 FROM hr_all_organization_units
134 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
135
136 BEGIN
137
138 OPEN inst_cur;
139 FETCH inst_cur INTO x_instance;
140 CLOSE inst_cur;
141
142 OPEN asg_cur;
143 FETCH asg_cur INTO
144 x_assignment_number,
145 x_assignment_id,
146 x_title,
147 x_normal_hours,
148 x_time_normal_start,
149 x_time_normal_end,
150 x_probation_period,
151 x_performance_review_period,
152 x_slry_review_period,
153 x_primary_flag,
154 x_normal_hours_frequency,
155 x_assignment_type,
156 x_manager_flag,
157 x_probation_period_unit,
158 x_perf_review_period_frqncy,
159 x_slry_review_period_frqncy,
160 x_hourly_salaried_flag;
161 CLOSE asg_cur;
162
163 OPEN ast_cur;
164 FETCH ast_cur INTO x_assignment_status_type_id;
165 CLOSE ast_cur;
166
167 OPEN pgr_cur;
168 FETCH pgr_cur INTO x_people_group_id;
169 CLOSE pgr_cur;
170
171 OPEN bgr_cur;
172 FETCH bgr_cur INTO x_business_group;
173 CLOSE bgr_cur;
174
175
176 x_assignment_pk := x_assignment_id + x_instance;
177 x_name := x_assignment_number + x_instance;
178 x_assignment_dp := x_assignment_number + x_instance;
179
180 x_total_assgnmnt := NVL(ceil(x_assignment_pk + 1), 0)
181 + NVL(ceil(x_name + 1), 0)
182 + NVL(ceil(x_assignment_dp + 1), 0)
183 + NVL(ceil(x_business_group + 1), 0)
184 + NVL(ceil(x_assignment_number + 1), 0)
185 + NVL(ceil(x_start_date + 1), 0)
186 + NVL(ceil(x_end_date + 1), 0)
187 + NVL(ceil(x_primary_flag + 1), 0)
188 + NVL(ceil(x_instance + 1), 0)
189 + NVL(ceil(x_assignment_id + 1), 0)
190 + NVL(ceil(x_assignment_status_type_id + 1), 0)
191 + NVL(ceil(x_business_group_id + 1), 0)
192 + NVL(ceil(x_people_group_id + 1), 0)
193 + NVL(ceil(x_effective_start_date + 1), 0)
194 + NVL(ceil(x_effective_end_date + 1), 0)
195 + NVL(ceil(x_title + 1), 0)
196 + NVL(ceil(x_normal_hours_frequency + 1), 0)
197 + NVL(ceil(x_normal_hours + 1), 0)
198 + NVL(ceil(x_assignment_status + 1), 0)
199 + NVL(ceil(x_assignment_type + 1), 0)
200 + NVL(ceil(x_manager_flag + 1), 0)
201 + NVL(ceil(x_time_normal_start + 1), 0)
202 + NVL(ceil(x_time_normal_end + 1), 0)
203 + NVL(ceil(x_probation_period_unit + 1), 0)
204 + NVL(ceil(x_probation_period + 1), 0)
205 + NVL(ceil(x_perf_review_period_frqncy + 1), 0)
206 + NVL(ceil(x_performance_review_period + 1), 0)
207 + NVL(ceil(x_slry_review_period_frqncy + 1), 0)
208 + NVL(ceil(x_slry_review_period + 1), 0)
209 + NVL(ceil(x_hourly_salaried_flag + 1), 0)
210 + NVL(ceil(x_last_update_date + 1), 0)
211 + NVL(ceil(x_creation_date + 1), 0);
212
213 /* TOTAL */
214
215 p_avg_row_length := x_total_assgnmnt;
216
217 END estimate_row_length;
218
219 END edw_hr_assgnmnt_m_sizing;