[Home] [Help]
PACKAGE BODY: APPS.BIM_EDW_SRCLSTS_M_SIZE
Source
1 PACKAGE BODY BIM_EDW_SRCLSTS_M_SIZE AS
2 /* $Header: bimszslb.pls 115.0 2001/03/14 12:03:51 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
16 ams_imp_list_headers_all ail ,
17 ams_list_src_types als ,
18 edw_local_instance inst
19 WHERE
20 ail.list_source_type_id = als.list_source_type_id(+)
21 and ail.import_type = 'SOURCE'
22 and ail.last_update_date between
23 p_from_date and p_to_date
24 );
25
26
27 BEGIN
28
29 dbms_output.enable(1000000);
30
31 OPEN c_cnt_rows;
32 FETCH c_cnt_rows INTO p_num_rows;
33 CLOSE c_cnt_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
47 x_name number;
48 x_import_list_header_id number;
49 x_list_source_type_id number;
50 x_user_status_id number;
51 x_status_code number;
52 x_status_date number;
53 x_vendor_id number;
54 x_transactional_cost number;
55 x_transactional_currency_code number;
56 x_functional_cost number;
57 x_functional_currency_code number;
58 x_pin_id number;
59 x_org_id number;
60 x_source_system number;
61 x_keywords number;
62 x_description number;
63
64 x_list_source_type number;
65
66 x_instance number;
67
68
69 CURSOR c_1 IS
70 SELECT
71 avg(nvl(vsize( name ), 0)),
72 avg(nvl(vsize( import_list_header_id ), 0)),
73 avg(nvl(vsize( list_source_type_id ), 0)),
74 avg(nvl(vsize( user_status_id ), 0)),
75 avg(nvl(vsize( status_code ), 0)),
76 avg(nvl(vsize( status_date ), 0)),
77 avg(nvl(vsize( vendor_id ), 0)),
78 avg(nvl(vsize( transactional_cost ), 0)),
79 avg(nvl(vsize( transactional_currency_code ), 0)),
80 avg(nvl(vsize( functional_cost ), 0)),
81 avg(nvl(vsize( functional_currency_code ), 0)),
82 avg(nvl(vsize( pin_id ), 0)),
83 avg(nvl(vsize( org_id ), 0)),
84 avg(nvl(vsize( source_system ), 0)),
85 avg(nvl(vsize( keywords ), 0)),
86 avg(nvl(vsize( description ), 0))
87 from
88 ams_imp_list_headers_all
89 where last_update_date between
90 p_from_date and p_to_date;
91
92
93
94 CURSOR c_2 IS
95 select
96 avg(nvl(vsize(INSTANCE_CODE), 0))
97 from EDW_LOCAL_INSTANCE ;
98
99
100 CURSOR c_3 IS
101 select
102 avg(nvl(vsize(list_source_type), 0))
103 from ams_list_src_types;
104
105 BEGIN
106
107 dbms_output.enable(1000000);
108
109 OPEN c_1;
110 FETCH c_1 INTO
111 x_name,
112 x_import_list_header_id,
113 x_list_source_type_id,
114 x_user_status_id,
115 x_status_code,
116 x_status_date,
117 x_vendor_id,
118 x_transactional_cost,
119 x_transactional_currency_code,
120 x_functional_cost,
121 x_functional_currency_code,
122 x_pin_id,
123 x_org_id,
124 x_source_system,
125 x_keywords,
126 x_description;
127
128
129 CLOSE c_1;
130
131 x_total := 5 +
132 3*ceil(X_name + 1) +
133 2*ceil(x_import_list_header_id + 1) +
134 ceil( x_list_source_type_id +1) +
135 ceil( x_user_status_id +1) +
136 ceil( x_status_code +1) +
137 ceil( x_status_date +1) +
138 ceil( x_vendor_id +1) +
139 ceil( x_transactional_cost +1) +
140 ceil( x_transactional_currency_code +1) +
141 ceil( x_functional_cost +1) +
142 ceil( x_functional_currency_code +1) +
143 ceil( x_pin_id +1) +
144 ceil( x_org_id +1) +
145 ceil( x_source_system +1) +
146 ceil( x_keywords +1) +
147 ceil( x_description +1) ;
148
149 OPEN c_2;
150 FETCH c_2 INTO x_INSTANCE;
151 CLOSE c_2;
152
153 x_total := x_total + 2*ceil(x_INSTANCE + 1);
154
155 OPEN c_3;
156 FETCH c_3 into x_list_source_type;
157 CLOSE c_3;
158
159
160 x_total := x_total + ceil(x_list_source_type +1) + 15*(x_constant + 1);
161
162 -- dbms_output.put_line(' ');
163 dbms_output.put_line('The average row length is : ' || to_char(x_total));
164
165 p_avg_row_len := x_total;
166
167 END; -- procedure est_row_len.
168
169 END; -- package body BIM_EDW_SRCLSTS_M_SIZE