[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_GEOGRAPHY_M_SIZE
Source
1 PACKAGE BODY POA_EDW_GEOGRAPHY_M_SIZE AS
2 /*$Header: poaszgyb.pls 120.0 2005/06/01 18:48:46 appldev noship $ */
3
4 PROCEDURE cnt_rows (p_from_date IN DATE,
5 p_to_date IN DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 BEGIN
9
10 -- dbms_output.enable(100000);
11
12 select sum(cnt) into p_num_rows
13 from (
14 select count(*) cnt
15 from
16 po_vendors vnd,
17 po_vendor_sites_all vns
18 WHERE vns.vendor_id = vnd.vendor_id
19 and greatest(vns.last_update_date, vnd.last_update_date)
20 between p_from_date and p_to_date
21 union all
22 select count(*) cnt
23 from hr_locations_all
24 where last_update_date between p_from_date and p_to_date
25 union all
26 select count(*) cnt
27 from
28 HZ_LOCATIONS hzl,
29 HZ_PARTIES hzp,
30 HZ_PARTY_SITES hzps
31 WHERE hzps.location_id = hzl.location_id
32 AND hzps.party_id = hzp.party_id
33 AND greatest(hzl.last_update_date, hzp.last_update_date,
34 hzps.last_update_date)
35 between p_from_date and p_to_date);
36
37 -- dbms_output.put_line('The number of rows for geography is: '
38 -- || to_char(p_num_rows));
39
40 EXCEPTION
41 WHEN OTHERS THEN p_num_rows := 0;
42 END;
43
44 -------------------------------------------------------
45
46 PROCEDURE est_row_len (p_from_date IN DATE,
47 p_to_date IN DATE,
48 p_avg_row_len OUT NOCOPY NUMBER) IS
49
50 x_areas number :=15;
51 x_total_country number := 0;
52 x_total_region number := 0;
53 x_total_state number := 0;
54 x_total_state_region number := 0;
55 x_total_city number := 0;
56 x_total_postcode number := 0;
57 x_total_postcode_city number := 0;
58 x_total_location number := 0;
59 x_total number := 0;
60 x_date number := 7;
61
62 x_org_id NUMBER := 0;
63 x_vendor_site_code NUMBER := 0;
64 x_province NUMBER := 0;
65 x_county NUMBER := 0;
66 x_state NUMBER := 0;
67 x_country NUMBER := 0;
68 x_postcode NUMBER := 0;
69 x_city NUMBER := 0;
70 x_vendor_name NUMBER := 0;
71 x_last_update_date NUMBER := x_date;
72 x_creation_date NUMBER := x_date;
73 x_town_or_city NUMBER := 0;
74 x_location_id NUMBER := 0;
75 x_postal_code NUMBER := 0;
76 x_region_2 NUMBER := 0;
77 x_location_code NUMBER := 0;
78 x_address1 NUMBER := 0;
79 x_address2 NUMBER := 0;
80 x_address3 NUMBER := 0;
81 x_address4 NUMBER := 0;
82 x_party_site_id NUMBER := 0;
83 x_party_site_name NUMBER := 0;
84 x_DESCRIPTION NUMBER := 0;
85 x_TERRITORY_SHORT_NAME NUMBER := 0;
86
87
88 x_ADDRESS_LINE_1 NUMBER := 0;
89 x_ADDRESS_LINE_2 NUMBER := 0;
90 x_ADDRESS_LINE_3 NUMBER := 0;
91 x_ADDRESS_LINE_4 NUMBER := 0;
92 x_LOCATION_DP NUMBER := 0;
93 x_LOCATION_PK NUMBER := 0;
94 x_POSTCODE_CITY_FK NUMBER := 0;
95 x_NAME NUMBER := 0;
96 x_INSTANCE NUMBER := 0;
97
98 x_POSTCODE_CITY_PK NUMBER := 0;
99 x_POSTCODE_CITY_DP NUMBER := 0;
100 x_CITY_FK NUMBER := 0;
101 x_POSTCODE_FK NUMBER := 0;
102
103 x_CITY_PK NUMBER := 0;
104 x_CITY_DP NUMBER := 0;
105
106 x_POSTCODE_PK NUMBER := 0;
107 x_POSTCODE_DP NUMBER := 0;
108 x_STATE_REGION_FK NUMBER := 0;
109
110 x_STATE_REGION_PK NUMBER := 0;
111 x_STATE_REGION_DP NUMBER := 0;
112 x_STATE_FK NUMBER := 0;
113
114 x_STATE_PK NUMBER := 0;
115 x_STATE_DP NUMBER := 0;
116 x_REGION_FK NUMBER := 0;
117
118 x_REGION_PK NUMBER := 0;
119 x_REGION_DP NUMBER := 0;
120 x_COUNTRY_FK NUMBER := 0;
121
122 x_COUNTRY_PK NUMBER := 0;
123 x_COUNTRY_DP NUMBER := 0;
124 x_AREA2_FK NUMBER := 0;
125 ------------------------------------------
126
127
128 cursor c0 is
129 select avg(nvl(vsize(instance_code),0))
130 from edw_local_instance;
131
132 cursor c1 is
133 select avg(nvl(vsize(vendor_site_id), 0)),
134 avg(nvl(vsize(org_id),0)),
135 avg(nvl(vsize(address_line1),0)),
136 avg(nvl(vsize(address_line2),0)),
137 avg(nvl(vsize(address_line3),0)),
138 avg(nvl(vsize(city),0)),
139 avg(nvl(vsize(county),0)),
140 avg(nvl(vsize(state),0)),
141 avg(nvl(vsize(zip),0)),
142 avg(nvl(vsize(province),0)),
143 avg(nvl(vsize(country),0)),
144 avg(nvl(vsize(vendor_site_code),0))
145 from po_vendor_sites_all where last_update_date
146 between p_from_date and p_to_date;
147
148 cursor c2 is
149 select avg(nvl(vsize(location_id),0)),
150 avg(nvl(vsize(town_or_city),0)),
151 avg(nvl(vsize(postal_code),0)),
152 avg(nvl(vsize(region_2),0)),
153 avg(nvl(vsize(country),0)),
154 avg(nvl(vsize(address_line_1),0)),
155 avg(nvl(vsize(address_line_2),0)),
156 avg(nvl(vsize(address_line_3),0)),
157 avg(nvl(vsize(location_code),0))
158 from HR_LOCATIONS_ALL where last_update_date
159 between p_from_date and p_to_date;
160
161 cursor c3 is
162 select avg(nvl(vsize(city),0)),
163 avg(nvl(vsize(postal_code),0)),
164 avg(nvl(vsize(state),0)),
165 avg(nvl(vsize(province),0)),
166 avg(nvl(vsize(country),0)),
167 avg(nvl(vsize(address1),0)),
168 avg(nvl(vsize(address2),0)),
169 avg(nvl(vsize(address3),0)),
170 avg(nvl(vsize(address4),0))
171 from HZ_LOCATIONS where last_update_date
172 between p_from_date and p_to_date;
173
174 cursor c4 is
175 select avg(nvl(vsize(party_site_id),0)),
176 avg(nvl(vsize(party_site_name),0))
177 from HZ_PARTY_SITES where last_update_date
178 between p_from_date and p_to_date;
179
180 cursor c5 is
181 select avg(nvl(vsize(DESCRIPTION),0)),
182 avg(nvl(vsize(TERRITORY_SHORT_NAME),0))
183 from fnd_territories_tl where last_update_date
184 between p_from_date and p_to_date;
185
186 cursor c6 is
187 select avg(nvl(vsize(vendor_name), 0))
188 from po_vendors where last_update_date
189 between p_from_date and p_to_date;
190
191
192 BEGIN
193 -- dbms_output.enable(100000);
194
195 -- dbms_output.put_line(' ');
196 -- dbms_output.put_line('input_m from source tables for the following staging tables are: ');
197 -- dbms_output.put_line(' ');
198 -- dbms_output.put_line('for EDW_GEOG_AREA1_LSTG : ' || to_char(x_areas));
199 -- dbms_output.put_line('for EDW_GEOG_AREA2_LSTG : ' || to_char(x_areas));
200
201 ---------------------------------------------------------------
202
203 open c0;
204 fetch c0 into x_INSTANCE;
205 close c0;
206
207 open c5;
208 fetch c5 into x_DESCRIPTION, x_TERRITORY_SHORT_NAME;
209 close c5;
210
211 ----------------- From PO_VENDOR_SITES ------------------
212
213 OPEN c1;
214 FETCH c1 into x_LOCATION_PK, x_org_id, x_ADDRESS_LINE_1,
215 x_ADDRESS_LINE_2, x_ADDRESS_LINE_3, x_city, x_county,
216 x_state, x_POSTCODE, x_province, x_country,
217 x_vendor_site_code;
218 CLOSE c1;
219
220 x_state := greatest (x_state, x_province);
221
222 open c6;
223 FETCH c6 into x_vendor_name;
224 close c6;
225
226 x_LOCATION_PK := x_LOCATION_PK + x_org_id;
227 x_LOCATION_DP := x_vendor_site_code + x_vendor_name;
228 x_POSTCODE_CITY_FK := x_city + x_state + x_POSTCODE + x_country;
229 x_NAME := x_LOCATION_DP;
230
231 x_total_location := x_total_location
232 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
233 + NVL (ceil(x_creation_date + 1), 0)
234 + NVL (ceil(x_LOCATION_PK + 1), 0)
235 + NVL (ceil(x_LOCATION_DP + 1), 0)
236 + NVL (ceil(x_NAME + 1), 0)
237 + NVL (ceil(x_ADDRESS_LINE_1 + 1), 0)
238 + NVL (ceil(x_ADDRESS_LINE_2 + 1), 0)
239 + NVL (ceil(x_ADDRESS_LINE_3 + 1), 0)
240 + NVL (ceil(x_POSTCODE_CITY_FK + 1), 0);
241
242
243 x_POSTCODE_CITY_PK := x_POSTCODE_CITY_FK;
244 x_POSTCODE_CITY_DP := x_city + x_state + x_POSTCODE;
245 x_CITY_FK := x_city + x_state + x_country;
246 x_POSTCODE_FK := x_state + x_POSTCODE + x_country;
247 x_NAME := x_POSTCODE_CITY_DP;
248
249 x_total_postcode_city := x_total_postcode_city
250 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
251 + NVL (ceil(x_creation_date + 1), 0)
252 + NVL (ceil(x_POSTCODE_CITY_PK + 1), 0)
253 + NVL (ceil(x_POSTCODE_CITY_DP + 1), 0)
254 + NVL (ceil(x_NAME + 1), 0)
255 + NVL (ceil(x_CITY_FK + 1), 0)
256 + NVL (ceil(x_POSTCODE_FK + 1), 0);
257
258
259 x_CITY_PK := x_CITY_FK;
260 x_CITY_DP := x_city + x_state;
261 x_NAME := x_CITY_DP;
262 x_STATE_REGION_FK := x_state + x_country;
263
264 x_total_city := x_total_city
265 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
266 + NVL (ceil(x_creation_date + 1), 0)
267 + NVL (ceil(x_CITY_PK + 1), 0)
268 + NVL (ceil(x_CITY_DP + 1), 0)
269 + NVL (ceil(x_NAME + 1), 0)
270 + NVL (ceil(x_STATE_REGION_FK + 1), 0);
271
272
273 x_POSTCODE_PK := x_POSTCODE_FK;
274 x_POSTCODE_DP := x_state + x_POSTCODE;
275 x_NAME := x_POSTCODE_DP;
276
277 x_total_postcode := x_total_postcode
278 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
279 + NVL (ceil(x_creation_date + 1), 0)
280 + NVL (ceil(x_POSTCODE_PK + 1), 0)
281 + NVL (ceil(x_POSTCODE_DP + 1), 0)
282 + NVL (ceil(x_NAME + 1), 0)
283 + NVL (ceil(x_STATE_REGION_FK + 1), 0);
284
285
286 x_STATE_REGION_PK := x_STATE_REGION_FK;
287 x_STATE_REGION_DP := x_state + x_country;
288 x_NAME := x_STATE_REGION_DP;
289 x_STATE_FK := x_state + x_country;
290
291 x_total_state_region := x_total_state_region
292 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
293 + NVL (ceil(x_creation_date + 1), 0)
294 + NVL (ceil(x_STATE_REGION_PK + 1), 0)
295 + NVL (ceil(x_STATE_REGION_DP + 1), 0)
296 + NVL (ceil(x_NAME + 1), 0)
297 + NVL (ceil(x_STATE_FK + 1), 0);
298
299
300 x_STATE_PK := x_STATE_FK;
301 x_STATE_DP := x_state + x_country;
302 x_NAME := x_STATE_DP;
303 x_REGION_FK := x_country;
304
305 x_total_state := x_total_state
306 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
307 + NVL (ceil(x_creation_date + 1), 0)
308 + NVL (ceil(x_STATE_PK + 1), 0)
309 + NVL (ceil(x_STATE_DP + 1), 0)
310 + NVL (ceil(x_NAME + 1), 0)
311 + NVL (ceil(x_REGION_FK + 1), 0);
312
313
314 x_REGION_PK := x_REGION_FK;
315 x_REGION_DP := x_country;
316 x_NAME := x_REGION_DP;
317 x_COUNTRY_FK := x_country;
318
319 x_total_region := x_total_region
320 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
321 + NVL (ceil(x_creation_date + 1), 0)
322 + NVL (ceil(x_REGION_PK + 1), 0)
323 + NVL (ceil(x_REGION_DP + 1), 0)
324 + NVL (ceil(x_NAME + 1), 0)
325 + NVL (ceil(x_COUNTRY_FK + 1), 0);
326
327
328 x_COUNTRY_PK := x_COUNTRY_FK;
329 x_COUNTRY_DP := x_DESCRIPTION;
330 x_NAME := x_TERRITORY_SHORT_NAME;
331 x_AREA2_FK := 3;
332
333 x_total_country := x_total_country
334 + NVL (ceil(x_INSTANCE + 1), 0) + NVL (ceil(x_last_update_date + 1), 0)
335 + NVL (ceil(x_creation_date + 1), 0)
336 + NVL (ceil(x_COUNTRY_PK + 1), 0)
337 + NVL (ceil(x_COUNTRY_DP + 1), 0)
338 + NVL (ceil(x_NAME + 1), 0)
339 + NVL (ceil(x_AREA2_FK + 1), 0);
340
341
342 ---------------- From HR_LOCATIONS -----------------
343
344 OPEN c2;
345 FETCH c2 into x_LOCATION_ID, x_town_or_city, x_postal_code,
346 x_region_2, x_country, x_ADDRESS_LINE_1,
347 x_ADDRESS_LINE_2, x_ADDRESS_LINE_3, x_location_code;
348 CLOSE c2;
349
350 x_LOCATION_PK := x_LOCATION_ID;
351 x_LOCATION_DP := x_location_code;
352 x_NAME := x_LOCATION_DP;
353 x_POSTCODE_CITY_FK := x_town_or_city + x_postal_code +
354 x_region_2 + x_country;
355
356 x_total_location := greatest(x_total_location
357 , NVL (ceil(x_LOCATION_PK + 1), 0)
358 + NVL (ceil(x_LOCATION_DP + 1), 0)
359 + NVL (ceil(x_NAME + 1), 0)
360 + NVL (ceil(x_ADDRESS_LINE_1 + 1), 0)
361 + NVL (ceil(x_ADDRESS_LINE_2 + 1), 0)
362 + NVL (ceil(x_ADDRESS_LINE_3 + 1), 0)
363 + NVL (ceil(x_POSTCODE_CITY_FK + 1), 0));
364
365
366 x_POSTCODE_CITY_PK := x_POSTCODE_CITY_FK;
367 x_POSTCODE_CITY_DP := x_town_or_city + x_postal_code + x_region_2;
368 x_CITY_FK := x_town_or_city + x_region_2 + x_country;
369 x_POSTCODE_FK := x_postal_code + x_region_2 + x_country;
370 x_NAME := x_POSTCODE_CITY_DP;
371
372 x_total_postcode_city := greatest(x_total_postcode_city
373 , NVL (ceil(x_POSTCODE_CITY_PK + 1), 0)
374 + NVL (ceil(x_POSTCODE_CITY_DP + 1), 0)
375 + NVL (ceil(x_NAME + 1), 0)
376 + NVL (ceil(x_CITY_FK + 1), 0)
377 + NVL (ceil(x_POSTCODE_FK + 1), 0));
378
379
380 x_CITY_PK := x_CITY_FK;
381 x_CITY_DP := x_town_or_city + x_region_2;
382 x_NAME := x_CITY_DP;
383 x_STATE_REGION_FK := x_region_2 + x_country;
384
385 x_total_city := greatest(x_total_city
386 , NVL (ceil(x_CITY_PK + 1), 0)
387 + NVL (ceil(x_CITY_DP + 1), 0)
388 + NVL (ceil(x_NAME + 1), 0)
389 + NVL (ceil(x_STATE_REGION_FK + 1), 0));
390
391
392 x_POSTCODE_PK := x_POSTCODE_FK;
393 x_POSTCODE_DP := x_postal_code + x_region_2;
394 x_NAME := x_POSTCODE_DP;
395
396 x_total_postcode := greatest(x_total_postcode
397 , NVL (ceil(x_POSTCODE_PK + 1), 0)
398 + NVL (ceil(x_POSTCODE_DP + 1), 0)
399 + NVL (ceil(x_NAME + 1), 0)
400 + NVL (ceil(x_STATE_REGION_FK + 1), 0));
401
402
403 x_STATE_REGION_PK := x_STATE_REGION_FK;
404 x_STATE_REGION_DP := x_region_2 + x_country;
405 x_NAME := x_STATE_REGION_DP;
406 x_STATE_FK := x_region_2 + x_country;
407
408 x_total_state_region := greatest(x_total_state_region
409 , NVL (ceil(x_STATE_REGION_PK + 1), 0)
410 + NVL (ceil(x_STATE_REGION_DP + 1), 0)
411 + NVL (ceil(x_NAME + 1), 0)
412 + NVL (ceil(x_STATE_FK + 1), 0));
413
414
418 x_REGION_FK := x_country;
415 x_STATE_PK := x_STATE_FK;
416 x_STATE_DP := x_region_2 + x_country;
417 x_NAME := x_STATE_DP;
419
420 x_total_state := greatest(x_total_state
421 , NVL (ceil(x_STATE_PK + 1), 0)
422 + NVL (ceil(x_STATE_DP + 1), 0)
423 + NVL (ceil(x_NAME + 1), 0)
424 + NVL (ceil(x_REGION_FK + 1), 0));
425
426
427 x_REGION_PK := x_REGION_FK;
428 x_REGION_DP := x_country;
429 x_NAME := x_REGION_DP;
430 x_COUNTRY_FK := x_country;
431
432 x_total_region := greatest(x_total_region
433 , NVL (ceil(x_REGION_PK + 1), 0)
434 + NVL (ceil(x_REGION_DP + 1), 0)
435 + NVL (ceil(x_NAME + 1), 0)
436 + NVL (ceil(x_COUNTRY_FK + 1), 0));
437
438
439 x_COUNTRY_PK := x_COUNTRY_FK;
440 x_COUNTRY_DP := x_DESCRIPTION;
441 x_NAME := x_TERRITORY_SHORT_NAME;
442 x_AREA2_FK := 3;
443
444 x_total_country := greatest(x_total_country
445 , NVL (ceil(x_COUNTRY_PK + 1), 0)
446 + NVL (ceil(x_COUNTRY_DP + 1), 0)
447 + NVL (ceil(x_NAME + 1), 0)
448 + NVL (ceil(x_AREA2_FK + 1), 0));
449
450
451 ----------------- From HZ_LOCATIONS and Party_Sites ------------------
452
453 OPEN c3;
454 FETCH c3 into x_city, x_postal_code, x_state, x_province,
455 x_country, x_ADDRESS1, x_ADDRESS2, x_ADDRESS3, x_ADDRESS4;
456 CLOSE c3;
457
458 x_state := greatest (x_state, x_province);
459
460 OPEN c4;
461 FETCH c4 into x_party_site_id, x_party_site_name;
462 CLOSE c4;
463
464
465 x_LOCATION_PK := x_party_site_id;
466 x_LOCATION_DP := x_party_site_name;
467 x_NAME := x_LOCATION_DP;
468 x_POSTCODE_CITY_FK := x_city + x_postal_code +
469 x_state + x_country;
470
471 x_total_location := greatest(x_total_location
472 , NVL (ceil(x_LOCATION_PK + 1), 0)
473 + NVL (ceil(x_LOCATION_DP + 1), 0)
474 + NVL (ceil(x_NAME + 1), 0)
475 + NVL (ceil(x_ADDRESS1 + 1), 0)
476 + NVL (ceil(x_ADDRESS2 + 1), 0)
477 + NVL (ceil(x_ADDRESS3 + 1), 0)
478 + NVL (ceil(x_ADDRESS4 + 1), 0)
479 + NVL (ceil(x_POSTCODE_CITY_FK + 1), 0));
480
481
482 x_POSTCODE_CITY_PK := x_POSTCODE_CITY_FK;
483 x_POSTCODE_CITY_DP := x_city + x_postal_code + x_state;
484 x_CITY_FK := x_city + x_state + x_country;
485 x_POSTCODE_FK := x_postal_code + x_state + x_country;
486 x_NAME := x_POSTCODE_CITY_DP;
487
488 x_total_postcode_city := greatest(x_total_postcode_city
489 , NVL (ceil(x_POSTCODE_CITY_PK + 1), 0)
490 + NVL (ceil(x_POSTCODE_CITY_DP + 1), 0)
491 + NVL (ceil(x_NAME + 1), 0)
492 + NVL (ceil(x_CITY_FK + 1), 0)
493 + NVL (ceil(x_POSTCODE_FK + 1), 0));
494
495
496 x_CITY_PK := x_CITY_FK;
497 x_CITY_DP := x_city + x_state;
498 x_NAME := x_CITY_DP;
499 x_STATE_REGION_FK := x_state + x_country;
500
501 x_total_city := greatest(x_total_city
502 , NVL (ceil(x_CITY_PK + 1), 0)
503 + NVL (ceil(x_CITY_DP + 1), 0)
504 + NVL (ceil(x_NAME + 1), 0)
505 + NVL (ceil(x_STATE_REGION_FK + 1), 0));
506
507
508 x_POSTCODE_PK := x_POSTCODE_FK;
509 x_POSTCODE_DP := x_postal_code + x_state;
510 x_NAME := x_POSTCODE_DP;
511
512 x_total_postcode := greatest(x_total_postcode
513 , NVL (ceil(x_POSTCODE_PK + 1), 0)
514 + NVL (ceil(x_POSTCODE_DP + 1), 0)
515 + NVL (ceil(x_NAME + 1), 0)
516 + NVL (ceil(x_STATE_REGION_FK + 1), 0));
517
518
519 x_STATE_REGION_PK := x_STATE_REGION_FK;
520 x_STATE_REGION_DP := x_state + x_country;
521 x_NAME := x_STATE_REGION_DP;
522 x_STATE_FK := x_state + x_country;
523
524 x_total_state_region := greatest(x_total_state_region
525 , NVL (ceil(x_STATE_REGION_PK + 1), 0)
526 + NVL (ceil(x_STATE_REGION_DP + 1), 0)
527 + NVL (ceil(x_NAME + 1), 0)
528 + NVL (ceil(x_STATE_FK + 1), 0));
529
530
531 x_STATE_PK := x_STATE_FK;
532 x_STATE_DP := x_state + x_country;
533 x_NAME := x_STATE_DP;
534 x_REGION_FK := x_country;
535
536 x_total_state := greatest(x_total_state
537 , NVL (ceil(x_STATE_PK + 1), 0)
538 + NVL (ceil(x_STATE_DP + 1), 0)
539 + NVL (ceil(x_NAME + 1), 0)
540 + NVL (ceil(x_REGION_FK + 1), 0));
541
542
543 x_REGION_PK := x_REGION_FK;
544 x_REGION_DP := x_country;
545 x_NAME := x_REGION_DP;
546 x_COUNTRY_FK := x_country;
547
548 x_total_region := greatest(x_total_region
549 , NVL (ceil(x_REGION_PK + 1), 0)
550 + NVL (ceil(x_REGION_DP + 1), 0)
551 + NVL (ceil(x_NAME + 1), 0)
552 + NVL (ceil(x_COUNTRY_FK + 1), 0));
553
554
555 x_COUNTRY_PK := x_COUNTRY_FK;
556 x_COUNTRY_DP := x_DESCRIPTION;
560 x_total_country := greatest(x_total_country
557 x_NAME := x_TERRITORY_SHORT_NAME;
558 x_AREA2_FK := 3;
559
561 , NVL (ceil(x_COUNTRY_PK + 1), 0)
562 + NVL (ceil(x_COUNTRY_DP + 1), 0)
563 + NVL (ceil(x_NAME + 1), 0)
564 + NVL (ceil(x_AREA2_FK + 1), 0));
565
566 ---------------------------------------------
567 x_total_country := 3 + x_total_country;
568 x_total_region := 3 + x_total_region;
569 x_total_state := 3 + x_total_state;
570 x_total_state_region := 3 + x_total_state_region;
571 x_total_city := 3 + x_total_city;
572 x_total_postcode := 3 + x_total_postcode;
573 x_total_postcode_city := 3 + x_total_postcode_city;
574 x_total_location := 3 + x_total_location;
575 ---------------------------------------------
576
577 -- dbms_output.put_line(' ');
578 -- dbms_output.put_line('for EDW_GEOG_COUNTRY_LSTG : ' || to_char(x_total_country));
579
580 -- dbms_output.put_line(' ');
581 -- dbms_output.put_line('for EDW_GEOG_REGION_LSTG : ' || to_char(x_total_region));
582
583 -- dbms_output.put_line(' ');
584 -- dbms_output.put_line('for EDW_GEOG_STATE_LSTG : ' || to_char(x_total_state));
585
586 -- dbms_output.put_line(' ');
587 -- dbms_output.put_line('for EDW_GEOG_STATE_REGION_LSTG : ' || to_char(x_total_state_region));
588
589 -- dbms_output.put_line(' ');
590 -- dbms_output.put_line('for EDW_GEOG_CITY_LSTG : ' || to_char(x_total_city));
591
592 -- dbms_output.put_line(' ');
593 -- dbms_output.put_line('for EDW_GEOG_POSTCODE_LSTG : ' || to_char(x_total_postcode));
594
595 -- dbms_output.put_line(' ');
596 -- dbms_output.put_line('for EDW_GEOG_POSTCODE_CITY_LSTG : ' || to_char(x_total_postcode_city));
597
598 -- dbms_output.put_line(' ');
599 -- dbms_output.put_line('for EDW_GEOG_LOCATION_LSTG : ' || to_char(x_total_location));
600
601
602 ---------------------------------------------------------------
603
604 x_total := 2*x_areas + x_total_country + x_total_region +
605 x_total_state + x_total_state_region + x_total_city +
606 x_total_postcode + x_total_postcode_city +
607 x_total_location;
608
609 -- dbms_output.put_line('-------------------------------------');
610 -- dbms_output.put_line('input_m for geography dimension is: '
611 -- || to_char(x_total));
612
613 p_avg_row_len := x_total;
614
615 EXCEPTION
616 WHEN OTHERS THEN p_avg_row_len := 0;
617 END; -- procedure est_row_len.
618
619 END;