PACKAGE BODY: APPS.BIM_EDW_MDCHN_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_MDCHN_M_SIZE AS
2 /* $Header: bimszmcb.pls 115.0 2001/03/14 12:02:58 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_media_vl ame,
16 	edw_local_instance inst
17         where ame.last_update_date between
18         p_from_date  and  p_to_date
19 	);
20 
21 
22 BEGIN
23 
24   dbms_output.enable(1000000);
25 
26   OPEN c_cnt_rows;
27        FETCH c_cnt_rows INTO p_num_rows;
28   CLOSE c_cnt_rows;
29 
30     dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
31 END;  -- procedure cnt_rows.
32 
33 
34 PROCEDURE est_row_len(p_from_date DATE,
35                       p_to_date DATE,
36                       p_avg_row_len OUT NUMBER) IS
37 
38  x_date                 number := 7;
39  x_total                number := 0;
40  x_constant             number := 6;
41  x_media_type_code number;
42  x_media_id number;
43  x_inbound_flag number;
44  x_enabled_flag number;
45  x_media_name number;
46  x_description number;
47 
48 
49  x_INSTANCE NUMBER;
50 
51 
52   CURSOR c_1 IS
53 	SELECT
54 	avg(nvl(vsize(media_type_code ), 0)),
55 	avg(nvl(vsize(media_id ), 0)),
56 	avg(nvl(vsize(inbound_flag ), 0)),
57 	avg(nvl(vsize(enabled_flag ), 0)),
58 	avg(nvl(vsize(media_name ), 0)),
59 	avg(nvl(vsize(description ), 0))
60         FROM ams_media_vl
61         where last_update_date between
62         p_from_date  and  p_to_date;
63 
64 
65   CURSOR c_2 IS
66 	select
67 	 avg(nvl(vsize(INSTANCE_CODE), 0))
68 	 from EDW_LOCAL_INSTANCE ;
69 
70 
71 
72   BEGIN
73 
74     dbms_output.enable(1000000);
75 
76     OPEN c_1;
77       FETCH c_1 INTO
78 	 x_media_type_code,
79 	 x_media_id,
80 	 x_inbound_flag,
81 	 x_enabled_flag,
82 	 x_media_name,
83 	 x_description;
84 
85     CLOSE c_1;
86 
87     x_total := 5  +
88 	ceil(	 x_media_type_code+1) +
89 	ceil(	 x_media_id+1) +
90 	2*ceil(	 x_inbound_flag+1) +
91 	ceil(	 x_enabled_flag+1) +
92 	2*ceil(	 x_media_name+1) +
93 	ceil(	 x_description);
94 
95     OPEN c_2;
96       FETCH c_2 INTO  x_INSTANCE;
97     CLOSE c_2;
98 
99     x_total := x_total + 3*ceil(x_INSTANCE + 1);
100     x_total := 4*x_total;
101 
102     x_total := x_total + 15*(x_constant + 1);
103 
104     -- dbms_output.put_line('     ');
105     dbms_output.put_line('The average row length is : ' || to_char(x_total));
106 
107   p_avg_row_len := x_total;
108 
109   END;  -- procedure est_row_len.
110 
111 END;  -- package body BIM_EDW_MDCHN_M_SIZE