[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_RQN_VCNCY_M_SIZING
Source
1 PACKAGE BODY edw_hr_rqn_vcncy_m_sizing AS
2 /* $Header: hriezvac.pkb 120.1 2005/06/08 02:50:53 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(vacancy_id) total
15 FROM per_all_vacancies
16 WHERE NVL(last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
17 BETWEEN p_from_date AND p_to_date;
18
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 /******************************************************************************/
30 /* Estimates row lengths. */
31 /******************************************************************************/
32 PROCEDURE estimate_row_length( p_from_date IN DATE,
33 p_to_date IN DATE,
34 p_avg_row_length OUT NOCOPY NUMBER )
35
36 IS
37
38 x_date NUMBER :=7;
39
40 x_total_req NUMBER;
41 x_total_vac NUMBER;
42
43 /* Requisition Level */
44 x_requisition_pk NUMBER:=0;
45 x_instance NUMBER:=0;
46 x_rqn_name NUMBER:=0;
47 x_requisition_dp NUMBER:=0;
48 x_business_group_id NUMBER:=0;
49 x_business_group NUMBER:=0;
50 x_requisition_id NUMBER:=0;
51 x_start_date NUMBER:= x_date;
52 x_end_date NUMBER:= x_date;
53 x_creation_date NUMBER:= x_date;
54 x_last_update_date NUMBER:= x_date;
55
56 /* Vacancy Level */
57 x_vacancy_pk NUMBER:=0;
58 x_requisition_fk NUMBER:=0;
59 x_vacancy_status_code NUMBER:=0;
60 x_vacancy_status NUMBER:=0;
61 x_number_of_openings NUMBER:=0;
62 x_budget_msrmnt_type_code NUMBER:=0;
63 x_budget_msrmnt_type NUMBER:=0;
64 x_budget_msrmnt_value NUMBER:=0;
65 x_vacancy_dp NUMBER:=0;
66 x_vac_name NUMBER:=0;
67 x_vacancy_id NUMBER:=0;
68 x_vacancy_start_date NUMBER:= x_date;
69 x_vacancy_end_date NUMBER:= x_date;
70
71 /* Selects the length of the instance code */
72 CURSOR inst_cur IS
73 SELECT avg(nvl(vsize(instance_code),0))
74 FROM edw_local_instance
75 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
76
77 CURSOR req_cur IS
78 SELECT avg(nvl(vsize(name),0))
79 ,avg(nvl(vsize(business_group_id),0))
80 ,avg(nvl(vsize(requisition_id),0))
81 FROM per_requisitions
82 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
83
84
85 CURSOR bus_cur IS
86 SELECT avg(nvl(vsize(name),0))
87 FROM hr_all_organization_units
88 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
89
90 CURSOR vcs_cur IS
91 SELECT
92 avg(nvl(vsize(vacancy_id),0))
93 ,avg(nvl(vsize(status),0))
94 ,avg(nvl(vsize(name),0))
95 ,avg(nvl(vsize(number_of_openings),0))
96 ,avg(nvl(vsize(budget_measurement_type),0))
97 ,avg(nvl(vsize(budget_measurement_value),0))
98 ,avg(nvl(vsize(business_group_id),0))
99 ,avg(nvl(vsize(requisition_id),0))
100 ,avg(nvl(vsize(vacancy_id),0))
101 ,avg(nvl(vsize(hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE',budget_measurement_type)),0))
102 ,avg(nvl(vsize(hr_general.decode_lookup('VACANCY_STATUS',status)),0))
103 FROM per_all_vacancies
104 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
105
106 BEGIN
107 /* Selects the length of the instance code */
108 OPEN inst_cur;
109 FETCH inst_cur INTO x_instance;
110 CLOSE inst_cur;
111
112 OPEN req_cur;
113 FETCH req_cur INTO
114 x_rqn_name
115 ,x_business_group_id
116 ,x_requisition_id;
117 CLOSE req_cur;
118
119 OPEN bus_cur;
120 FETCH bus_cur INTO x_business_group;
121 CLOSE bus_cur;
122
123 OPEN vcs_cur;
124 FETCH vcs_cur INTO
125 x_vacancy_id
126 ,x_vacancy_status_code
127 ,x_vac_name
128 ,x_number_of_openings
129 ,x_budget_msrmnt_type_code
130 ,x_budget_msrmnt_value
131 ,x_business_group_id
132 ,x_requisition_id
133 ,x_vacancy_id
134 ,x_budget_msrmnt_type
135 ,x_vacancy_status;
136 CLOSE vcs_cur;
137
138 /* Requisition Level */
139
140 x_requisition_pk := x_requisition_id + x_instance;
141 x_rqn_name := x_rqn_name + x_business_group;
142 x_requisition_dp := x_rqn_name;
143
144 x_total_req := NVL (ceil(x_requisition_pk + 1), 0)
145 + NVL (ceil(x_instance + 1), 0)
146 + NVL (ceil(x_rqn_name + 1), 0)
147 + NVL (ceil(x_requisition_dp + 1), 0)
148 + NVL (ceil( x_business_group_id + 1), 0)
149 + NVL (ceil(x_requisition_id + 1), 0)
150 + NVL (ceil(x_start_date + 1), 0)
151 + NVL (ceil( x_end_date + 1), 0)
152 + NVL (ceil( x_creation_date + 1), 0)
153 + NVL (ceil(x_last_update_date + 1), 0);
154
155
156 /* Vacancy Level */
157
158 x_vacancy_pk := x_requisition_pk + x_vacancy_id + x_instance;
159 x_requisition_pk := x_requisition_fk;
160 x_vac_name := x_vac_name + x_business_group;
161 x_requisition_dp := x_vac_name;
162
163 x_total_vac := NVL (ceil(x_vacancy_pk + 1), 0)
164 + NVL (ceil(x_requisition_fk + 1), 0)
165 + NVL (ceil(x_instance + 1), 0)
166 + NVL (ceil(x_vacancy_status_code + 1), 0)
167 + NVL (ceil(x_vacancy_status + 1), 0)
168 + NVL (ceil(x_number_of_openings + 1), 0)
169 + NVL (ceil(x_budget_msrmnt_type_code + 1), 0)
170 + NVL (ceil(x_budget_msrmnt_type + 1), 0)
171 + NVL (ceil(x_budget_msrmnt_value + 1), 0)
172 + NVL (ceil(x_vac_name + 1), 0)
173 + NVL (ceil(x_vacancy_dp + 1), 0)
174 + NVL (ceil(x_business_group_id + 1), 0)
175 + NVL (ceil(x_requisition_id + 1), 0)
176 + NVL (ceil(x_vacancy_id + 1), 0)
177 + NVL (ceil(x_vacancy_start_date + 1), 0)
178 + NVL (ceil(x_vacancy_end_date + 1), 0) ;
179
180 p_avg_row_length := x_total_req + x_total_vac;
181
182
183 END estimate_row_length;
184
185 END edw_hr_rqn_vcncy_m_sizing;