[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_POSITION_M_SIZING
Source
1 PACKAGE BODY edw_hr_position_m_sizing AS
2 /* $Header: hriezpos.pkb 120.1 2005/06/08 02:48:05 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(position_id) total
16 FROM per_all_positions
17 WHERE NVL(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_total_pos NUMBER;
40
41 x_position_pk NUMBER:=0;
42 x_instance NUMBER:=0;
43 x_pos_name NUMBER:=0;
44 x_name NUMBER:=0;
45 x_position_dp NUMBER:=0;
46 x_business_group NUMBER:=0;
47 x_all_fk NUMBER:=0;
48 x_organization_id NUMBER:=0;
49 x_job_id NUMBER:=0;
50 x_position_id NUMBER:=0;
51 x_position_definition_id NUMBER:=0;
52 x_business_group_id NUMBER:=0;
53 x_position_from_date NUMBER:= x_date;
54 x_position_to_date NUMBER:= x_date;
55 x_probation_period NUMBER:=0;
56 x_replacement_required NUMBER:=0;
57 x_time_normal_start NUMBER:=0;
58 x_time_normal_finish NUMBER:=0;
59 x_working_hours NUMBER:=0;
60 x_working_hour_freq NUMBER:=0;
61 x_last_update_date NUMBER:= x_date;
62 x_creation_date NUMBER:= x_date;
63
64
65 /* Select the length of the instance code */
66 CURSOR inst_cur IS
67 SELECT avg(nvl( vsize(instance_code),0 ))
68 FROM edw_local_instance;
69
70 CURSOR pos_cur IS
71 SELECT
72 avg(nvl(vsize(name),0))
73 ,avg(nvl(vsize(organization_id),0))
74 ,avg(nvl(vsize(job_id),0))
75 ,avg(nvl(vsize(position_id),0))
76 ,avg(nvl(vsize(position_definition_id),0))
77 ,avg(nvl(vsize(business_group_id),0))
78 ,avg(nvl(vsize(probation_period),0))
79 ,avg(nvl(vsize(replacement_required_flag),0))
80 ,avg(nvl(vsize(time_normal_start),0))
81 ,avg(nvl(vsize(time_normal_finish),0))
82 ,avg(nvl(vsize(working_hours),0))
83 ,avg(nvl(vsize(hr_general.decode_lookup('FREQUENCY',frequency)),0))
84 FROM per_all_positions
85 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
86
87 CURSOR bgr_cur IS
88 SELECT
89 avg(nvl(vsize(name),0))
90 FROM hr_all_organization_units
91 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
92
93 BEGIN
94
95 OPEN inst_cur;
96 FETCH inst_cur INTO x_instance;
97 CLOSE inst_cur;
98
99 OPEN pos_cur;
100 FETCH pos_cur INTO
101 x_pos_name
102 ,x_organization_id
103 ,x_job_id
104 ,x_position_id
105 ,x_position_definition_id
106 ,x_business_group_id
107 ,x_probation_period
108 ,x_replacement_required
109 ,x_time_normal_start
110 ,x_time_normal_finish
111 ,x_working_hours
112 ,x_working_hour_freq;
113 CLOSE pos_cur;
114
115 OPEN bgr_cur;
116 FETCH bgr_cur INTO x_business_group;
117 CLOSE bgr_cur;
118
119 x_position_pk := x_position_id + x_instance;
120 x_name := x_pos_name + x_business_group + x_instance;
121 x_name := x_position_dp;
122
123 x_total_pos := NVL(ceil(x_position_pk + 1), 0)
124 + NVL(ceil(x_instance + 1), 0)
125 + NVL(ceil(x_name + 1), 0)
126 + NVL(ceil(x_position_dp + 1), 0)
127 + NVL(ceil(x_business_group + 1), 0)
128 + NVL(ceil(x_organization_id + 1), 0)
129 + NVL(ceil(x_job_id + 1), 0)
130 + NVL(ceil(x_position_id + 1), 0)
131 + NVL(ceil(x_position_definition_id + 1), 0)
132 + NVL(ceil(x_business_group_id + 1), 0)
133 + NVL(ceil(x_position_from_date + 1), 0)
134 + NVL(ceil(x_position_to_date + 1), 0)
135 + NVL(ceil(x_probation_period + 1), 0)
136 + NVL(ceil(x_replacement_required + 1), 0)
137 + NVL(ceil(x_time_normal_start + 1), 0)
138 + NVL(ceil(x_time_normal_finish + 1), 0)
139 + NVL(ceil(x_working_hours + 1), 0)
140 + NVL(ceil(x_working_hour_freq + 1), 0)
141 + NVL(ceil(x_last_update_date + 1), 0)
142 + NVL(ceil(x_creation_date + 1), 0);
143
144 p_avg_row_length := x_total_pos;
145
146 END estimate_row_length;
147
148
149 END edw_hr_position_m_sizing;