PACKAGE BODY: APPS.BIM_EDW_OPPORTUNITIES_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_OPPORTUNITIES_M_SIZE AS
2 /* $Header: bimszopb.pls 115.0 2001/03/14 12:03:23 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_LEAD_LINES_ALL OPL ,
16 	AS_LEADS_ALL OPH ,
17 	EDW_LOCAL_INSTANCE INST
18 	WHERE
19 	OPH.LEAD_ID = OPL.LEAD_ID AND ( (OPH.LAST_UPDATE_DATE > TO_DATE
20 	('1000/01/01',
21 	 'YYYY/MM/DD')) OR (OPL.LAST_UPDATE_DATE > TO_DATE('1000/01/01',
22 	 'YYYY/MM/DD')) ) AND ( OPH.DELETED_FLAG IS NULL OR OPH.DELETED_FLAG <> 'Y' )
23         and oph.last_update_date between
24         p_from_date  and  p_to_date
25         );
26 
27 
28 BEGIN
29 
30   dbms_output.enable(1000000);
31 
32   OPEN c_cnt_rows;
33        FETCH c_cnt_rows INTO p_num_rows;
34   CLOSE c_cnt_rows;
35 
36     dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
37 END;  -- procedure cnt_rows.
38 
39 
40 PROCEDURE est_row_len(p_from_date DATE,
41                       p_to_date DATE,
42                       p_avg_row_len OUT NUMBER) IS
43 
44  x_date                 NUMBER := 7;
45  x_total                NUMBER := 0;
46  x_constant             NUMBER := 6;
47  X_LEAD_LINE_ID		NUMBER;
48  X_LEAD_ID   		NUMBER;
49  X_INSTANCE NUMBER;
50 
51 
52   CURSOR c_1 IS
53 	SELECT
54 	avg(nvl(vsize( LEAD_LINE_ID ), 0))
55         FROM AS_LEAD_LINES_ALL;
56 
57 
58   CURSOR c_2 IS
59 	select
60 	 avg(nvl(vsize(INSTANCE_CODE), 0))
61 	 from EDW_LOCAL_INSTANCE ;
62 
63   CURSOR c_3 IS
64 	SELECT
65 	avg(nvl(vsize( LEAD_ID ), 0))
66         FROM AS_LEADS_ALL
67         where last_update_date between
68         p_from_date  and  p_to_date;
69 
70 
71   BEGIN
72 
73     dbms_output.enable(1000000);
74 
75     OPEN c_1;
76       FETCH c_1 INTO
77 	 X_LEAD_LINE_ID   ;
78 
79     CLOSE c_1;
80 
81     x_total := 5  +
82 		4*ceil(	 X_LEAD_LINE_ID +1);
83 
84 
85     OPEN c_2;
86       FETCH c_2 INTO  x_INSTANCE;
87     CLOSE c_2;
88 
89     OPEN c_3;
90       FETCH c_3 INTO
91 	 X_LEAD_ID   ;
92 
93     CLOSE c_3;
94 
95     x_total := 2*(x_total +
96 		5*ceil(x_INSTANCE + 1) +
97 		2*ceil(x_LEAD_ID + 1)) +
98     		15*(x_constant + 1);
99 
100     -- dbms_output.put_line('     ');
101     dbms_output.put_line('The average row length is : ' || to_char(x_total));
102 
103   p_avg_row_len := x_total;
104 
105   END;  -- procedure est_row_len.
106 
107 END;  -- package body BIM_EDW_OPPORTUNITIES_M_SIZE