PACKAGE BODY: APPS.BIM_EDW_OFFERS_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_OFFERS_M_SIZE AS
2 /* $Header: bimszofb.pls 115.0 2001/03/14 12:03:18 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 ams_act_offers aao ,
16 	qp_list_headers_vl qlh ,
17 	edw_local_instance inst
18 	WHERE aao.qp_list_header_id = qlh.list_header_id and
19 	((aao.last_update_date > to_date('1000/01/01', 'YYYY/MM/DD'))
20 	or (qlh.last_update_date > to_date('1000/01/01', 'YYYY/MM/DD')))
21         and aao.last_update_date between
22         p_from_date  and  p_to_date
23 	);
24 
25 
26 BEGIN
27 
28   dbms_output.enable(1000000);
29 
30   OPEN c_cnt_rows;
31        FETCH c_cnt_rows INTO p_num_rows;
32   CLOSE c_cnt_rows;
33 
34     dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
35 END;  -- procedure cnt_rows.
36 
37 
38 PROCEDURE est_row_len(p_from_date DATE,
39                       p_to_date DATE,
40                       p_avg_row_len OUT NUMBER) IS
41 
42  x_date                 number := 7;
43  x_total                number := 0;
44  x_constant             number := 6;
45 
46 
47  x_activity_offer_id number;
48  x_offer_code number;
49  x_offer_type number;
50  x_primary_offer_flag number;
51 
52  x_list_header_id number;
53  x_name number;
54  x_description number;
55 
56  x_INSTANCE NUMBER;
57 
58 
59   CURSOR c_1 IS
60 	SELECT
61 	avg(nvl(vsize(activity_offer_id ), 0)),
62 	avg(nvl(vsize(offer_code ), 0)),
63 	avg(nvl(vsize(offer_type ), 0)),
64 	avg(nvl(vsize(primary_offer_flag ), 0))
65         FROM ams_act_offers
66         where last_update_date between
67         p_from_date  and  p_to_date;
68 
69 
70   CURSOR c_2 IS
71 	select
72 	 avg(nvl(vsize(INSTANCE_CODE), 0))
73 	 from EDW_LOCAL_INSTANCE ;
74 
75   CURSOR c_3 IS
76 	select
77 	avg(nvl(vsize(name ), 0)),
78 	avg(nvl(vsize(list_header_id ), 0)),
79 	avg(nvl(vsize(description ), 0))
80         FROM qp_list_headers_vl;
81 
82   BEGIN
83 
84     dbms_output.enable(1000000);
85 
86     OPEN c_1;
87       FETCH c_1 INTO
88 	 x_activity_offer_id,
89 	 x_offer_code,
90 	 x_offer_type,
91 	 x_primary_offer_flag;
92 
93     CLOSE c_1;
94 
95     x_total := 57  +
96 	 2*ceil(x_activity_offer_id + 1) +
97 	 ceil(x_offer_code + 1) +
98 	 ceil(x_offer_type + 1) +
99 	 ceil(x_primary_offer_flag + 1);
100 
101     OPEN c_2;
102       FETCH c_2 INTO  x_INSTANCE;
103     CLOSE c_2;
104 
105     x_total := x_total + 2*ceil(x_INSTANCE + 1);
106 
107     OPEN c_3;
108       FETCH c_3 INTO
109 	 x_name,
110 	 x_list_header_id,
111 	 x_description;
112     CLOSE c_3;
113 
114     x_total := x_total +
115                ceil(x_name + 1) +
116 	       ceil(x_list_header_id + 1) +
117 	       ceil(x_description + 1) +
118                15*(x_constant + 1);
119 
120     -- dbms_output.put_line('     ');
121     dbms_output.put_line('The average row length is : ' || to_char(x_total));
122 
123   p_avg_row_len := x_total;
124 
125   END;  -- procedure est_row_len.
126 
127 END;  -- package body BIM_EDW_OFFERS_M_SIZE