[Home] [Help]
PACKAGE BODY: APPS.BIM_EDW_OPP_STATUS_M_SIZE
Source
1 PACKAGE BODY BIM_EDW_OPP_STATUS_M_SIZE AS
2 /* $Header: bimszosb.pls 115.0 2001/03/14 12:03:29 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 AS_STATUSES_VL AST ,
16 EDW_LOCAL_INSTANCE INST
17 WHERE AST.OPP_FLAG = 'Y' AND AST.ENABLED_FLAG = 'Y'
18 and ast.last_update_date between
19 p_from_date and p_to_date
20 );
21
22
23 BEGIN
24
25 dbms_output.enable(1000000);
26
27 OPEN c_cnt_rows;
28 FETCH c_cnt_rows INTO p_num_rows;
29 CLOSE c_cnt_rows;
30
31 dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
32 END; -- procedure cnt_rows.
33
34
35 PROCEDURE est_row_len(p_from_date DATE,
36 p_to_date DATE,
37 p_avg_row_len OUT NUMBER) IS
38
39 x_date number := 7;
40 x_total number := 0;
41 x_constant number := 6;
42
43 X_STATUS_CODE NUMBER;
44 X_MEANING NUMBER;
45 X_DESCRIPTION NUMBER;
46 X_OPP_OPEN_STATUS_FLAG NUMBER;
47 X_FORECAST_ROLLUP_FLAG NUMBER;
48
49 x_INSTANCE NUMBER;
50
51
52 CURSOR c_1 IS
53 SELECT
54 avg(nvl(vsize(MEANING ), 0)),
55 avg(nvl(vsize(STATUS_CODE ), 0)),
56 avg(nvl(vsize(DESCRIPTION ), 0)),
57 avg(nvl(vsize(OPP_OPEN_STATUS_FLAG ), 0)),
58 avg(nvl(vsize(FORECAST_ROLLUP_FLAG ), 0))
59 FROM AS_STATUSES_VL AST
60 WHERE AST.OPP_FLAG = 'Y' AND AST.ENABLED_FLAG = 'Y'
61 and AST.last_update_date between
62 p_from_date and p_to_date;
63
64
65
66 CURSOR c_2 IS
67 select
68 avg(nvl(vsize(INSTANCE_CODE), 0))
69 from EDW_LOCAL_INSTANCE ;
70
71
72
73 BEGIN
74
75 dbms_output.enable(1000000);
76
77 OPEN c_1;
78 FETCH c_1 INTO
79 X_MEANING,
80 X_STATUS_CODE,
81 X_DESCRIPTION,
82 X_OPP_OPEN_STATUS_FLAG ,
83 X_FORECAST_ROLLUP_FLAG ;
84
85 CLOSE c_1;
86
87 x_total := 6 +
88 2*ceil(X_STATUS_CODE + 1) +
89 3*ceil(X_MEANING + 1) +
90 ceil(X_DESCRIPTION + 1) +
91 ceil(X_OPP_OPEN_STATUS_FLAG + 1) +
92 ceil(X_FORECAST_ROLLUP_FLAG + 1);
93
94 OPEN c_2;
95 FETCH c_2 INTO x_INSTANCE;
96 CLOSE c_2;
97
98 x_total := x_total + 2*ceil(x_INSTANCE + 1);
99
100 x_total := x_total + 15*(x_constant + 1);
101
102 -- dbms_output.put_line(' ');
103 dbms_output.put_line('The average row length is : ' || to_char(x_total));
104
105 p_avg_row_len := x_total;
106
107 END; -- procedure est_row_len.
108
109 END; -- package body BIM_EDW_OPP_STATUS_M_SIZE