PACKAGE BODY: APPS.BIM_EDW_LEAD_STATUS_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_LEAD_STATUS_M_SIZE AS
2 /* $Header: bimszlsb.pls 115.0 2001/03/14 12:02: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 AS_STATUSES_VL AST ,
16 	EDW_LOCAL_INSTANCE INST
17 	WHERE AST.LEAD_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_MEANING NUMBER;
44  X_STATUS_CODE NUMBER;
45  X_DESCRIPTION NUMBER;
46 
47  x_INSTANCE NUMBER;
48 
49 
50   CURSOR c_1 IS
51 	SELECT
52 	avg(nvl(vsize(MEANING ), 0)),
53 	avg(nvl(vsize(STATUS_CODE ), 0)),
54 	avg(nvl(vsize(DESCRIPTION ), 0))
55         FROM AS_STATUSES_VL
56         where last_update_date between
57         p_from_date  and  p_to_date ;
58 
59 
60   CURSOR c_2 IS
61 	select
62 	 avg(nvl(vsize(INSTANCE_CODE), 0))
63 	 from EDW_LOCAL_INSTANCE ;
64 
65 
66 
67   BEGIN
68 
69     dbms_output.enable(1000000);
70 
71     OPEN c_1;
72       FETCH c_1 INTO
73 	 X_MEANING,
74 	 X_STATUS_CODE,
75 	 X_DESCRIPTION;
76 
77     CLOSE c_1;
78 
79     x_total := 5  +
80 	 2*ceil(X_STATUS_CODE + 1) +
81 	 3*ceil(X_MEANING + 1) +
82 	 ceil(X_DESCRIPTION + 1);
83 
84     OPEN c_2;
85       FETCH c_2 INTO  x_INSTANCE;
86     CLOSE c_2;
87 
88     x_total := x_total + 2*ceil(x_INSTANCE + 1);
89 
90     x_total := x_total + 15*(x_constant + 1);
91 
92     -- dbms_output.put_line('     ');
93     dbms_output.put_line('The average row length is : ' || to_char(x_total));
94 
95   p_avg_row_len := x_total;
96 
97   END;  -- procedure est_row_len.
98 
99 END;  -- package body BIM_EDW_LEAD_STATUS_M_SIZE