[Home] [Help]
PACKAGE BODY: APPS.EDW_ORGANIZATION_M_SIZING
Source
1 PACKAGE BODY edw_organization_m_sizing AS
2 /* $Header: hriezorg.pkb 120.1 2005/06/08 02:47:42 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(*) total
16 FROM
17 hr_all_organization_units org
18 ,per_business_groups bgr
19 ,hr_organization_information oi1
20 ,hr_all_organization_units ou
21 ,mtl_parameters mp
22 ,hr_organization_information oi2
23 ,hri_primary_hrchys tree
24 WHERE org.business_group_id = bgr.business_group_id
25 AND org.organization_id = oi1.organization_id (+)
26 AND oi1.org_information_context (+) = 'Accounting Information'
27 AND to_number(oi1.org_information3) = ou.organization_id (+)
28 AND org.organization_id = mp.organization_id (+)
29 AND org.organization_id = oi2.organization_id (+)
30 AND oi2.org_information_context (+) = 'CLASS'
31 AND oi2.org_information1 (+) = 'OPERATING_UNIT'
32 AND oi2.org_information2 (+) = 'Y'
33 AND tree.organization_id (+) = org.organization_id
34 AND greatest( NVL(org.last_update_date, to_date('01-01-2000','DD-MM-YYYY')),
35 NVL(ou.last_update_date, to_date('01-01-2000','DD-MM-YYYY')),
36 NVL(tree.org_last_updated,to_date('01-01-2000','DD-MM-YYYY')),
37 NVL(oi1.last_update_date, to_date('01-01-2000','DD-MM-YYYY')),
38 NVL(mp.last_update_date, to_date('01-01-2000','DD-MM-YYYY')),
39 NVL(oi2.last_update_date, to_date('01-01-2000','DD-MM-YYYY')))
40 BETWEEN p_from_date AND p_to_date;
41
42 BEGIN
43
44 OPEN row_count_cur;
45 FETCH row_count_cur INTO p_row_count;
46 CLOSE row_count_cur;
47
48 END count_source_rows;
49
50
51 /******************************************************************************/
52 /* Estimates row lengths. */
53 /******************************************************************************/
54 PROCEDURE estimate_row_length( p_from_date IN DATE,
55 p_to_date IN DATE,
56 p_avg_row_length OUT NOCOPY NUMBER )
57 IS
58
59 x_date NUMBER := 7;
60
61 x_total_business_grp NUMBER;
62 x_total_leg_entity NUMBER;
63 x_total_oper_unit NUMBER;
64 x_total_tree1_lvl8 NUMBER;
65 x_total_tree1_lvl7 NUMBER;
66 x_total_tree1_lvl6 NUMBER;
67 x_total_tree1_lvl5 NUMBER;
68 x_total_tree1_lvl4 NUMBER;
69 x_total_tree1_lvl3 NUMBER;
70 x_total_tree1_lvl2 NUMBER;
71 x_total_tree1_lvl1 NUMBER;
72 x_total_organization NUMBER;
73
74 /* Business Group Level */
75 x_business_group_dp NUMBER := 0;
76 x_business_group_pk NUMBER := 0;
77 x_creation_date NUMBER := x_date;
78 x_date_from NUMBER := x_date;
79 x_date_to NUMBER := x_date;
80 x_instance NUMBER := 0;
81 x_int_ext_flag NUMBER := 0;
82 x_last_update_date NUMBER := x_date;
83 x_bg_name NUMBER := 0;
84 x_org_code NUMBER := 0;
85 x_org_type NUMBER := 0;
86 x_primary_cst_mthd NUMBER := 0;
87 x_business_group_id NUMBER := 0;
88 x_cost_allocation NUMBER := 0;
89 x_legislation NUMBER := 0;
90
91 /* Level Entity Level */
92 x_business_group_fk NUMBER := 0;
93 x_legal_entity_dp NUMBER := 0;
94 x_legal_entity_pk NUMBER := 0;
95 x_level_name NUMBER := 2;
96 x_lg_name NUMBER := 0;
97 x_set_of_books NUMBER := 0;
98
99 /* Operating Unit Level */
100 x_business_grp NUMBER := 0;
101 x_legal_entity_fk NUMBER := 0;
102 x_ou_name NUMBER := 0;
103 x_operating_unit_dp NUMBER := 0;
104 x_operating_unit_pk NUMBER := 0;
105
106 /* Organization Level */
107 x_org_name NUMBER := 0;
108 x_operating_unit_fk NUMBER := 0;
109 x_organization_pk NUMBER := 0;
110 x_organization_dp NUMBER := 0;
111 x_org_tree1_lvl1_fk NUMBER := 0;
112
113 /* Tree 1 Level 8 */
114 x_l8_name NUMBER := 0;
115 x_org_tree1_lvl8_pk NUMBER := 0;
116
117 /* Tree 1 Level 7 */
118 x_l7_name NUMBER := 0;
119 x_org_tree1_lvl7_pk NUMBER := 0;
120 x_org_tree1_lvl8_fk NUMBER := 0;
121
122 /* Tree 1 Level 6 */
123 x_l6_name NUMBER := 0;
124 x_org_tree1_lvl6_pk NUMBER := 0;
125 x_org_tree1_lvl7_fk NUMBER := 0;
126
127 /* Tree 1 Level 5 */
128 x_l5_name NUMBER := 0;
129 x_org_tree1_lvl5_pk NUMBER := 0;
130 x_org_tree1_lvl6_fk NUMBER := 0;
131
132 /* Tree 1 Level 4 */
133 x_l4_name NUMBER := 0;
134 x_org_tree1_lvl4_pk NUMBER := 0;
135 x_org_tree1_lvl5_fk NUMBER := 0;
136
137 /* Tree 1 Level 3 */
138 x_l3_name NUMBER := 0;
139 x_org_tree1_lvl3_pk NUMBER := 0;
140 x_org_tree1_lvl4_fk NUMBER := 0;
141
142 /* Tree 1 Level 2 */
143 x_l2_name NUMBER := 0;
144 x_org_tree1_lvl2_pk NUMBER := 0;
145 x_org_tree1_lvl3_fk NUMBER := 0;
146
147 /* Tree 1 Level 1 */
148 x_l1_name NUMBER := 0;
149 x_org_tree1_lvl1_pk NUMBER := 0;
150 x_org_tree1_lvl2_fk NUMBER := 0;
151
152 /* Selects the length of the instance code */
153 CURSOR inst_cur IS
154 SELECT avg(nvl(vsize(instance_code),0))
155 FROM edw_local_instance;
156
157 CURSOR org_cur IS
158 SELECT
159 avg(nvl(vsize(name),0))
160 ,avg(nvl(vsize(hr_general.decode_lookup('INTL_EXTL',internal_external_flag)),0))
161 ,avg(nvl(vsize(type),0))
162 ,avg(nvl(vsize(hr_general.decode_lookup('ORG_TYPE',type)),0))
163 ,avg(nvl(vsize(organization_id),0))
164 FROM hr_all_organization_units
165 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
166
167 CURSOR pcm_cur IS
168 SELECT avg(nvl(vsize(primary_cost_method),0))
169 FROM mtl_parameters
170 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
171
172 CURSOR cst_cur IS
173 SELECT avg(nvl(vsize(id_flex_structure_name),0))
174 FROM fnd_id_flex_structures_vl
175 WHERE application_id = 801
176 AND id_flex_code = 'COST'
177 AND last_update_date BETWEEN p_from_date AND p_to_date;
178
179 CURSOR leg_cur IS
180 SELECT avg(nvl(vsize(territory_short_name),0))
181 FROM fnd_territories_vl
182 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
183
184 CURSOR sob_cur IS
185 SELECT avg(nvl(vsize(name),0))
186 FROM gl_sets_of_books
187 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
188
189 BEGIN
190
191 OPEN inst_cur;
192 FETCH inst_cur INTO x_instance;
193 CLOSE inst_cur;
194
195 OPEN org_cur;
196 FETCH org_cur INTO x_bg_name, x_int_ext_flag, x_org_code, x_org_type, x_business_group_id;
197 CLOSE org_cur;
198
199 OPEN pcm_cur;
200 FETCH pcm_cur INTO x_primary_cst_mthd;
201 CLOSE pcm_cur;
202
203 OPEN cst_cur;
204 FETCH cst_cur INTO x_cost_allocation;
205 CLOSE cst_cur;
206
207 OPEN leg_cur;
208 FETCH leg_cur INTO x_legislation;
209 CLOSE leg_cur;
210
211 OPEN sob_cur;
212 FETCH sob_cur INTO x_set_of_books;
213 CLOSE sob_cur;
214
215 /* Business Group Level */
216
217 x_business_group_pk := x_business_group_id + x_instance;
218 x_business_group_dp := x_bg_name;
219
220 x_total_business_grp := NVL (ceil(x_business_group_pk + 1), 0)
221 + NVL (ceil(x_business_group_dp + 1), 0)
222 + NVL (ceil(x_creation_date + 1), 0)
223 + NVL (ceil(x_date_from + 1), 0)
224 + NVL (ceil(x_date_to + 1), 0)
225 + NVL (ceil(x_instance + 1), 0)
226 + NVL (ceil(x_int_ext_flag + 1), 0)
227 + NVL (ceil(x_last_update_date + 1), 0)
228 + NVL (ceil(x_bg_name + 1), 0)
229 + NVL (ceil(x_org_type + 1), 0)
230 + NVL (ceil(x_org_code + 1), 0)
231 + NVL (ceil(x_primary_cst_mthd + 1), 0)
232 + NVL (ceil(x_business_group_id + 1), 0)
233 + NVL (ceil(x_cost_allocation + 1), 0)
234 + NVL (ceil(x_legislation + 1), 0);
235
236 /* Legal Entity Level */
237
238 x_legal_entity_pk := x_business_group_id + x_instance;
239 x_business_group_fk := x_business_group_pk;
240 x_lg_name := 2 * x_bg_name;
241 x_legal_entity_dp := x_lg_name;
242
243 x_total_leg_entity := NVL (ceil(x_business_group_fk + 1), 0)
244 + NVL (ceil(x_creation_date + 1), 0)
245 + NVL (ceil(x_date_from + 1), 0)
246 + NVL (ceil(x_date_to + 1), 0)
247 + NVL (ceil(x_instance + 1), 0)
248 + NVL (ceil(x_int_ext_flag + 1), 0)
249 + NVL (ceil(x_last_update_date + 1), 0)
250 + NVL (ceil(x_legal_entity_dp + 1), 0)
251 + NVL (ceil(x_legal_entity_pk + 1), 0)
252 + NVL (ceil(x_level_name + 1), 0)
253 + NVL (ceil(x_lg_name + 1), 0)
254 + NVL (ceil(x_org_type + 1), 0)
255 + NVL (ceil(x_org_code + 1), 0)
256 + NVL (ceil(x_primary_cst_mthd + 1), 0)
257 + NVL (ceil(x_set_of_books + 1), 0)
258 + NVL (ceil(x_business_group_id + 1), 0);
259
260 /* Operating Unit Level */
261
262 x_operating_unit_pk := x_business_group_id + x_instance;
263 x_business_grp := x_bg_name;
264 x_legal_entity_fk := x_legal_entity_pk;
265 x_ou_name := x_lg_name;
266 x_operating_unit_dp := x_ou_name;
267
268 x_total_oper_unit := NVL (ceil(x_business_grp + 1), 0)
269 + NVL (ceil(x_creation_date + 1), 0)
270 + NVL (ceil(x_date_from + 1), 0)
271 + NVL (ceil(x_date_to + 1), 0)
272 + NVL (ceil(x_instance + 1), 0)
273 + NVL (ceil(x_int_ext_flag + 1), 0)
274 + NVL (ceil(x_last_update_date + 1), 0)
275 + NVL (ceil(x_legal_entity_fk + 1), 0)
276 + NVL (ceil(x_level_name + 1), 0)
277 + NVL (ceil(x_ou_name + 1), 0)
278 + NVL (ceil(x_operating_unit_dp + 1), 0)
279 + NVL (ceil(x_operating_unit_pk + 1), 0)
280 + NVL (ceil(x_org_type + 1), 0)
281 + NVL (ceil(x_org_code + 1), 0)
282 + NVL (ceil(x_primary_cst_mthd + 1), 0)
283 + NVL (ceil(x_business_group_id + 1), 0);
284
285 /* Organization Level */
286
287 x_organization_pk := x_business_group_id + x_instance;
288 x_operating_unit_fk := x_operating_unit_pk;
289 x_org_tree1_lvl1_fk := x_business_group_id + x_instance;
290 x_org_name := x_ou_name;
291 x_organization_dp := x_org_name;
292
293 x_total_organization := NVL (ceil(x_business_grp + 1), 0)
294 + NVL (ceil(x_creation_date + 1), 0)
295 + NVL (ceil(x_date_from + 1), 0)
296 + NVL (ceil(x_date_to + 1), 0)
297 + NVL (ceil(x_instance + 1), 0)
298 + NVL (ceil(x_int_ext_flag + 1), 0)
299 + NVL (ceil(x_last_update_date + 1), 0)
300 + NVL (ceil(x_level_name + 1), 0)
301 + NVL (ceil(x_org_name + 1), 0)
302 + NVL (ceil(x_operating_unit_fk + 1), 0)
303 + NVL (ceil(x_organization_dp + 1), 0)
304 + NVL (ceil(x_organization_pk + 1), 0)
305 + NVL (ceil(x_org_type + 1), 0)
306 + NVL (ceil(x_org_code + 1), 0)
307 + NVL (ceil(x_primary_cst_mthd + 1), 0)
308 + NVL (ceil(x_business_group_id + 1), 0)
309 + NVL (ceil(x_org_tree1_lvl1_fk + 1), 0);
310
311 /* Tree 1 Level 8 */
312
313 x_org_tree1_lvl8_pk := x_business_group_id + x_instance;
314 x_l8_name := x_org_name;
315
316 x_total_tree1_lvl8 := NVL (ceil(x_business_grp + 1), 0)
317 + NVL (ceil(x_creation_date + 1), 0)
318 + NVL (ceil(x_instance + 1), 0)
319 + NVL (ceil(x_last_update_date + 1), 0)
320 + NVL (ceil(x_l8_name + 1), 0)
321 + NVL (ceil(x_business_group_id + 1), 0)
322 + NVL (ceil(x_org_tree1_lvl8_pk + 1), 0)
323 + NVL (ceil(x_l8_name + 1), 0);
324
325 /* Tree 1 Level 7 */
326
327 x_org_tree1_lvl7_pk := x_business_group_id + x_instance;
328 x_org_tree1_lvl8_fk := x_org_tree1_lvl8_pk;
329 x_l7_name := x_org_name;
330
331 x_total_tree1_lvl7 := NVL (ceil(x_business_grp + 1), 0)
332 + NVL (ceil(x_creation_date + 1), 0)
333 + NVL (ceil(x_instance + 1), 0)
334 + NVL (ceil(x_last_update_date + 1), 0)
335 + NVL (ceil(x_l7_name + 1), 0)
336 + NVL (ceil(x_business_group_id + 1), 0)
337 + NVL (ceil(x_org_tree1_lvl7_pk + 1), 0)
338 + NVL (ceil(x_org_tree1_lvl8_fk + 1), 0)
339 + NVL (ceil(x_l8_name + 1), 0);
340
341 /* Tree 1 Level 1 - 6 (same as Level 7) */
342
343 /* TOTAL */
344
345 p_avg_row_length := x_total_business_grp
346 + x_total_leg_entity
347 + x_total_oper_unit
348 + x_total_organization
349 + x_total_tree1_lvl8
350 + (7 * x_total_tree1_lvl7);
351
352 END estimate_row_length;
353
354 END edw_organization_m_sizing;