[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_SPRTN_F_SIZING
Source
1 PACKAGE BODY hr_edw_wrk_sprtn_f_sizing AS
2 /* $Header: hriezwsp.pkb 120.1 2005/06/08 02:52:46 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(pps.period_of_service_id) total
15 FROM per_periods_of_service pps
16 WHERE NVL(pps.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 /* Estimates row lengths. */
29 /******************************************************************************/
30 PROCEDURE estimate_row_length( p_from_date IN DATE,
31 p_to_date IN DATE,
32 p_avg_row_length OUT NOCOPY NUMBER )
33
34 IS
35
36 x_date NUMBER :=7;
37
38 x_total_wsp NUMBER;
39
40 x_separation_pk NUMBER :=0;
41 x_assignment_fk NUMBER :=0;
42 x_age_band_fk NUMBER :=0;
43 x_service_band_fk NUMBER :=0;
44 x_geography_fk NUMBER :=0;
45 x_grade_fk NUMBER :=0;
46 x_instance_fk NUMBER :=0;
47 x_job_fk NUMBER :=0;
48 x_organization_fk NUMBER :=0;
49 x_person_fk NUMBER :=0;
50 x_person_type_fk NUMBER :=0;
51 x_position_fk NUMBER :=0;
52 x_time_trm_ntfd_fk NUMBER :=0;
53 x_time_emp_strt_fk NUMBER :=0;
54 x_time_trm_accptd_fk NUMBER :=0;
55 x_time_trm_prjctd_fk NUMBER :=0;
56 x_time_trm_prcss_fk NUMBER :=0;
57 x_time_trm_occrd_fk NUMBER :=0;
58 x_reason_fk NUMBER :=0;
59 x_movement_type_fk NUMBER :=0;
60
61 x_asg_assignment_id NUMBER :=0;
62 x_asg_business_group_id NUMBER :=0;
63 x_asg_grade_id NUMBER :=0;
64 x_asg_job_id NUMBER :=0;
65 x_asg_location_id NUMBER :=0;
66 x_asg_organization_id NUMBER :=0;
67 x_asg_person_id NUMBER :=0;
68 x_asg_position_id NUMBER :=0;
69
70 x_pps_prd_of_srvc_id NUMBER :=0;
71 x_pps_trm_acptd_prsn_id NUMBER :=0;
72 x_leaving_reason NUMBER :=0;
73
74 x_date_of_birth NUMBER :=x_date;
75 x_last_update_date NUMBER :=x_date;
76 x_creation_date NUMBER :=x_date;
77 x_ntfd_trmntn_dt NUMBER :=x_date;
78 x_accptd_trmntn_dt NUMBER :=x_date;
79 x_prjctd_trmntn_dt NUMBER :=x_date;
80 x_actual_trmntn_dt NUMBER :=x_date;
81 x_final_process_dt NUMBER :=x_date;
82
83 /* Select the length of the instance code */
84 CURSOR inst_cur IS
85 SELECT avg(nvl( vsize(instance_code),0 ))
86 FROM edw_local_instance;
87
88 CURSOR pps_cur IS
89 SELECT
90 avg(nvl(vsize(pps.period_of_service_id),0 ))
91 ,avg(nvl(vsize(pps.termination_accepted_person_id),0 ))
92 ,avg(nvl(vsize(pps.leaving_reason),0 ))
93 FROM per_periods_of_service pps
94 WHERE pps.last_update_date BETWEEN p_from_date AND p_to_date;
95
96 CURSOR pasg_cur IS
97 SELECT
98 avg(nvl(vsize(pasg.assignment_id),0 ))
99 ,avg(nvl(vsize(pasg.business_group_id),0 ))
100 ,avg(nvl(vsize(pasg.grade_id),0 ))
101 ,avg(nvl(vsize(pasg.job_id ),0 ))
102 ,avg(nvl(vsize(pasg.location_id ),0 ))
103 ,avg(nvl(vsize(pasg.organization_id ),0 ))
104 ,avg(nvl(vsize(pasg.person_id ),0 ))
105 ,avg(nvl(vsize(pasg.position_id ),0 ))
106 FROM per_all_assignments_f pasg
107 WHERE pasg.last_update_date BETWEEN p_from_date AND p_to_date;
108
109 BEGIN
110
111 OPEN inst_cur;
112 FETCH inst_cur INTO x_instance_fk;
113 CLOSE inst_cur;
114
115 OPEN pps_cur;
116 FETCH pps_cur INTO
117 x_pps_prd_of_srvc_id
118 ,x_pps_trm_acptd_prsn_id
119 ,x_leaving_reason;
120 CLOSE pps_cur;
121
122 OPEN pasg_cur;
123 FETCH pasg_cur INTO
124 x_asg_assignment_id
125 ,x_asg_business_group_id
126 ,x_asg_grade_id
127 ,x_asg_job_id
128 ,x_asg_location_id
129 ,x_asg_organization_id
130 ,x_asg_person_id
131 ,x_asg_position_id;
132 CLOSE pasg_cur;
133
134 x_separation_pk := x_pps_prd_of_srvc_id + x_asg_person_id + x_instance_fk;
135
136 x_assignment_fk := hri_edw_dim_sizing.get_size_asg_pk;
137 x_age_band_fk := hri_edw_dim_sizing.get_size_agb_pk;
138 x_service_band_fk := hri_edw_dim_sizing.get_size_lwb_pk;
139 x_geography_fk := hri_edw_dim_sizing.get_size_geog_pk;
140 x_grade_fk := hri_edw_dim_sizing.get_size_grd_pk;
141 x_job_fk := hri_edw_dim_sizing.get_size_job_pk;
142 x_organization_fk := hri_edw_dim_sizing.get_size_org_pk;
143 x_person_fk := hri_edw_dim_sizing.get_size_psn_pk;
144 x_person_type_fk := hri_edw_dim_sizing.get_size_pty_pk;
145 x_position_fk := hri_edw_dim_sizing.get_size_pos_pk;
146 x_time_trm_accptd_fk := hri_edw_dim_sizing.get_size_time_pk;
147 x_time_trm_ntfd_fk := x_time_trm_accptd_fk;
148 x_time_emp_strt_fk := x_time_trm_accptd_fk;
149 x_time_trm_prjctd_fk := x_time_trm_accptd_fk;
150 x_time_trm_prcss_fk := x_time_trm_accptd_fk;
151 x_time_trm_occrd_fk := x_time_trm_accptd_fk;
152 x_reason_fk := hri_edw_dim_sizing.get_size_rsn_pk;
153 x_movement_type_fk := hri_edw_dim_sizing.get_size_mvt_pk;
154
155
156 x_total_wsp := NVL(ceil( x_separation_pk + 1), 0)
157 + NVL(ceil( x_assignment_fk + 1), 0)
158 + NVL(ceil( x_age_band_fk + 1), 0)
159 + NVL(ceil( x_service_band_fk + 1), 0)
160 + NVL(ceil( x_geography_fk + 1), 0)
161 + NVL(ceil( x_grade_fk + 1), 0)
162 + NVL(ceil( x_instance_fk + 1), 0)
163 + NVL(ceil( x_job_fk + 1), 0)
164 + NVL(ceil( x_organization_fk + 1), 0)
165 + NVL(ceil( x_person_fk + 1), 0)
166 + NVL(ceil( x_person_type_fk + 1), 0)
167 + NVL(ceil( x_position_fk + 1), 0)
168 + NVL(ceil( x_time_trm_ntfd_fk + 1), 0)
169 + NVL(ceil( x_time_emp_strt_fk + 1), 0)
170 + NVL(ceil( x_time_trm_accptd_fk + 1), 0)
171 + NVL(ceil( x_time_trm_prjctd_fk + 1), 0)
172 + NVL(ceil( x_time_trm_prcss_fk + 1), 0)
173 + NVL(ceil( x_time_trm_occrd_fk + 1), 0)
174 + NVL(ceil( x_reason_fk + 1), 0)
175 + NVL(ceil( x_movement_type_fk + 1), 0)
176 + NVL(ceil( x_asg_assignment_id + 1), 0)
177 + NVL(ceil( x_asg_business_group_id + 1), 0)
178 + NVL(ceil( x_asg_grade_id + 1), 0)
179 + NVL(ceil( x_asg_job_id + 1), 0)
180 + NVL(ceil( x_asg_location_id + 1), 0)
181 + NVL(ceil( x_asg_organization_id + 1), 0)
182 + NVL(ceil( x_asg_person_id + 1), 0)
183 + NVL(ceil( x_asg_position_id + 1), 0)
184 + NVL(ceil( x_pps_prd_of_srvc_id + 1), 0)
185 + NVL(ceil( x_pps_trm_acptd_prsn_id + 1), 0)
186 + NVL(ceil( x_date_of_birth + 1), 0)
187 + NVL(ceil( x_last_update_date + 1), 0)
188 + NVL(ceil( x_creation_date + 1), 0)
189 + NVL(ceil( x_ntfd_trmntn_dt + 1), 0)
190 + NVL(ceil( x_accptd_trmntn_dt + 1), 0)
191 + NVL(ceil( x_prjctd_trmntn_dt + 1), 0)
192 + NVL(ceil( x_actual_trmntn_dt + 1), 0)
193 + NVL(ceil( x_final_process_dt + 1), 0)
194 + NVL(ceil( x_leaving_reason + 1), 0);
195
196 /* TOTAL */
197
198 p_avg_row_length := x_total_wsp;
199
200 END estimate_row_length;
201
202 END hr_edw_wrk_sprtn_f_sizing;