PACKAGE BODY: APPS.BIM_EDW_IH_RESLT_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_IH_RESLT_M_SIZE AS
2 /* $Header: bimszrtb.pls 115.0 2001/03/14 12:03:46 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 JTF_IH_RESULTS_VL RSLT ,
16 	EDW_LOCAL_INSTANCE INST
17         where RSLT.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_RESULT_ID NUMBER;
42  X_SHORT_DESCRIPTION NUMBER;
43  X_RESULT_CODE NUMBER;
44  X_POSITIVE_RESPONSE_FLAG NUMBER;
45  X_LONG_DESCRIPTION NUMBER;
46 
47  x_INSTANCE NUMBER;
48 
49 
50   CURSOR c_1 IS
51 	SELECT
52 	avg(nvl(vsize(RESULT_ID ), 0)),
53 	avg(nvl(vsize(SHORT_DESCRIPTION ), 0)),
54 	avg(nvl(vsize(RESULT_CODE ), 0)),
55 	avg(nvl(vsize(POSITIVE_RESPONSE_FLAG ), 0)),
56 	avg(nvl(vsize(LONG_DESCRIPTION ), 0))
57         FROM JTF_IH_RESULTS_VL
58         where
59         last_update_date between
60         p_from_date  and  p_to_date;
61 
62 
63   CURSOR c_2 IS
64 	select
65 	 avg(nvl(vsize(INSTANCE_CODE), 0))
66 	 from EDW_LOCAL_INSTANCE ;
67 
68 
69 
70   BEGIN
71 
72     dbms_output.enable(1000000);
73 
74     OPEN c_1;
75       FETCH c_1 INTO
76 	 X_RESULT_ID,
77 	 X_SHORT_DESCRIPTION,
78 	 X_RESULT_CODE,
79 	 X_POSITIVE_RESPONSE_FLAG,
80 	 X_LONG_DESCRIPTION;
81 
82     CLOSE c_1;
83 
84     x_total := 4  +
85 	 ceil(X_RESULT_ID + 1) +
86 	 3*ceil(X_SHORT_DESCRIPTION + 1) +
87 	 ceil(X_RESULT_CODE + 1) +
88 	 ceil(X_POSITIVE_RESPONSE_FLAG + 1) +
89 	 ceil(X_LONG_DESCRIPTION + 1);
90 
91     OPEN c_2;
92       FETCH c_2 INTO  x_INSTANCE;
93     CLOSE c_2;
94 
95     x_total := x_total + 2*ceil(x_INSTANCE + 1);
96 
97     x_total := x_total + 15*(x_constant + 1);
98 
99     -- dbms_output.put_line('     ');
100     dbms_output.put_line('The average row length is : ' || to_char(x_total));
101 
102   p_avg_row_len := x_total;
103 
104   END;  -- procedure est_row_len.
105 
106 END;  -- package body BIM_EDW_IH_RESLT_M_SIZE