[Home] [Help]
PACKAGE BODY: APPS.BIM_EDW_CMPSTATS_M_SIZE
Source
1 PACKAGE BODY BIM_EDW_CMPSTATS_M_SIZE AS
2 /* $Header: bimszcsb.pls 115.0 2001/03/14 12:01: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_user_statuses_vl aus ,
17 edw_local_instance inst
18 WHERE aus.system_status_type = 'AMS_CAMPAIGN_STATUS' and
19 aus.last_update_date between
20 p_from_date and p_to_date
21 );
22
23
24 BEGIN
25
26 dbms_output.enable(1000000);
27
28 OPEN c_cnt_rows;
29 FETCH c_cnt_rows INTO p_num_rows;
30 CLOSE c_cnt_rows;
31
32 dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
33 END; -- procedure cnt_rows.
34
35
36 PROCEDURE est_row_len(p_from_date DATE,
37 p_to_date DATE,
38 p_avg_row_len OUT NUMBER) IS
39
40 x_date number := 7;
41 x_total number := 0;
42 x_constant number := 6;
43 x_NAME NUMBER;
44 x_USER_STATUS_ID NUMBER;
45 x_SYSTEM_STATUS_CODE NUMBER;
46 x_SYSTEM_STATUS_TYPE NUMBER;
47 x_ENABLED_FLAG NUMBER;
48 x_DEFAULT_FLAG NUMBER;
49 x_SEEDED_FLAG NUMBER;
50 x_START_DATE_ACTIVE NUMBER;
51 x_END_DATE_ACTIVE NUMBER;
52 x_DESCRIPTION NUMBER;
53 x_CREATION_DATE NUMBER;
54 x_LAST_UPDATE_DATE NUMBER;
55 x_INSTANCE NUMBER;
56
57
58 CURSOR c_1 IS
59 SELECT
60 avg(nvl(vsize(NAME), 0)),
61 avg(nvl(vsize(USER_STATUS_ID), 0)),
62 avg(nvl(vsize(SYSTEM_STATUS_CODE), 0)),
63 avg(nvl(vsize(SYSTEM_STATUS_TYPE), 0)),
64 avg(nvl(vsize(ENABLED_FLAG), 0)),
65 avg(nvl(vsize(DEFAULT_FLAG), 0)),
66 avg(nvl(vsize(SEEDED_FLAG), 0)),
67 avg(nvl(vsize(START_DATE_ACTIVE), 0)),
68 avg(nvl(vsize(END_DATE_ACTIVE), 0)),
69 avg(nvl(vsize(DESCRIPTION), 0)),
70 avg(nvl(vsize(CREATION_DATE), 0)),
71 avg(nvl(vsize(LAST_UPDATE_DATE), 0))
72 FROM ams_user_statuses_vl
73 where last_update_date between
74 p_from_date and p_to_date;
75
76
77
78 CURSOR c_2 IS
79 select
80 avg(nvl(vsize(INSTANCE_CODE), 0))
81 from EDW_LOCAL_INSTANCE ;
82
83
84
85 BEGIN
86
87 dbms_output.enable(1000000);
88
89 OPEN c_1;
90 FETCH c_1 INTO
91 x_NAME,
92 x_USER_STATUS_ID,
93 x_SYSTEM_STATUS_CODE,
94 x_SYSTEM_STATUS_TYPE,
95 x_ENABLED_FLAG,
96 x_DEFAULT_FLAG,
97 x_SEEDED_FLAG,
98 x_START_DATE_ACTIVE,
99 x_END_DATE_ACTIVE,
100 x_DESCRIPTION,
101 x_CREATION_DATE,
102 x_LAST_UPDATE_DATE;
103
104 CLOSE c_1;
105
106 x_total := 20 +
107 ceil(x_NAME + 1) +
108 ceil(x_USER_STATUS_ID + 1) +
109 ceil(x_SYSTEM_STATUS_CODE + 1) +
110 ceil(x_SYSTEM_STATUS_TYPE + 1) +
111 ceil(x_ENABLED_FLAG + 1) +
112 ceil(x_DEFAULT_FLAG + 1) +
113 ceil(x_SEEDED_FLAG + 1) +
114 ceil(x_START_DATE_ACTIVE + 1) +
115 ceil(x_END_DATE_ACTIVE + 1) +
116 ceil(x_DESCRIPTION + 1) +
117 ceil(x_CREATION_DATE + 1) +
118 ceil(x_LAST_UPDATE_DATE + 1);
119
120 OPEN c_2;
121 FETCH c_2 INTO x_INSTANCE;
122 CLOSE c_2;
123
124 x_total := x_total + 3*ceil(x_INSTANCE + 1);
125
126 x_total := x_total + 15*(x_constant + 1);
127
128 -- dbms_output.put_line(' ');
129 dbms_output.put_line('The average row length is : ' || to_char(x_total));
130
131 p_avg_row_len := x_total;
132
133 END; -- procedure est_row_len.
134
135 END; -- package body BIM_EDW_CMPSTATS_M_SIZE