[Home] [Help]
PACKAGE BODY: APPS.BIM_EDW_TGSMT_M_SIZE
Source
1 PACKAGE BODY BIM_EDW_TGSMT_M_SIZE AS
2 /* $Header: bimsztsb.pls 115.0 2001/03/14 12:03:56 pkm ship $*/
3
4 PROCEDURE cnt_rows(p_from_date DATE,
5 p_to_date DATE,
6 p_num_rows OUT NUMBER) IS
7
8
9 -- v_num_rows NUMBER := 0;
10
11 CURSOR c_cnt_rows IS
12 select sum(cnt)
13 from (
14 select count(*) cnt
15 FROM AMS_CELLS_VL AMC ,
16 EDW_BIM_TGSMT_DENORM BTD ,
17 EDW_LOCAL_INSTANCE INST
18 WHERE AMC.CELL_ID = BTD.TARGET_SEGMENT_ID AND BTD.LEVEL_FROM_ROOT >= 8
19 AND BTD.PARENT_LEVEL_FROM_ROOT = 7
20 and amc.last_update_date between
21 p_from_date and p_to_date
22 );
23
24
25 BEGIN
26
27 dbms_output.enable(1000000);
28
29 OPEN c_cnt_rows;
30 FETCH c_cnt_rows INTO p_num_rows;
31 CLOSE c_cnt_rows;
32
33 p_num_rows := 9*p_num_rows;
34
35 dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
36 END; -- procedure cnt_rows.
37
38
39 PROCEDURE est_row_len(p_from_date DATE,
40 p_to_date DATE,
41 p_avg_row_len OUT NUMBER) IS
42
43 x_date number := 7;
44 x_total number := 0;
45 x_constant number := 6;
46 X_CELL_NAME NUMBER;
47 X_CELL_ID NUMBER;
48 X_CELL_CODE NUMBER;
49 X_MARKET_SEGMENT_FLAG NUMBER;
50 X_ENABLED_FLAG NUMBER;
51 X_ORIGINAL_SIZE NUMBER;
52 X_DESCRIPTION NUMBER;
53
54 x_INSTANCE NUMBER;
55
56
57 CURSOR c_1 IS
58 SELECT
59 avg(nvl(vsize(AMC.CELL_NAME), 0)),
60 avg(nvl(vsize(AMC.CELL_ID), 0)),
61 avg(nvl(vsize(AMC.CELL_CODE), 0)),
62 avg(nvl(vsize(AMC.MARKET_SEGMENT_FLAG), 0)),
63 avg(nvl(vsize(AMC.ENABLED_FLAG), 0)),
64 avg(nvl(vsize(AMC.ORIGINAL_SIZE), 0)),
65 avg(nvl(vsize(AMC.DESCRIPTION), 0))
66 FROM AMS_CELLS_VL AMC
67 WHERE
68 amc.last_update_date between
69 p_from_date and p_to_date;
70
71
72
73 CURSOR c_2 IS
74 select
75 avg(nvl(vsize(INSTANCE_CODE), 0))
76 from EDW_LOCAL_INSTANCE ;
77
78
79 BEGIN
80
81 dbms_output.enable(1000000);
82
83 OPEN c_1;
84 FETCH c_1 INTO
85 X_CELL_NAME,
86 X_CELL_ID,
87 X_CELL_CODE,
88 X_MARKET_SEGMENT_FLAG,
89 X_ENABLED_FLAG,
90 X_ORIGINAL_SIZE,
91 X_DESCRIPTION;
92
93 CLOSE c_1;
94
95 x_total := 20 +
96 3*ceil( X_CELL_NAME+1) +
97 ceil( X_CELL_ID+1) +
98 2*ceil( X_CELL_CODE+1) +
99 ceil( X_MARKET_SEGMENT_FLAG+1) +
100 ceil( X_ENABLED_FLAG+1) +
101 ceil( X_ORIGINAL_SIZE+1) +
102 ceil( X_DESCRIPTION+1);
103
104
105
106 OPEN c_2;
107 FETCH c_2 INTO x_INSTANCE;
108 CLOSE c_2;
109
110 x_total := x_total + 3*ceil(x_INSTANCE + 1);
111
112 x_total := 9*x_total + 15*(x_constant + 1);
113
114 -- dbms_output.put_line(' ');
115 dbms_output.put_line('The average row length is : ' || to_char(x_total));
116
117 p_avg_row_len := x_total;
118
119 END; -- procedure est_row_len.
120
121 END; -- package body BIM_EDW_TGSMT_M_SIZE