PACKAGE BODY: APPS.BIM_EDW_LEADS_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_LEADS_M_SIZE AS
2 /* $Header: bimszldb.pls 115.0 2001/03/14 12:02:45 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_SALES_LEAD_LINES ASLL ,
16 	AS_SALES_LEADS ASL ,
17 	EDW_LOCAL_INSTANCE INST
18 	WHERE ASLL.SALES_LEAD_ID = ASL.SALES_LEAD_ID AND
19 	( (ASL.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') )
20 	OR (ASLL.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') ) )
21 	AND ( ASL.DELETED_FLAG IS NULL OR ASL.DELETED_FLAG <> 'Y' )
22         and
23         asl.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_SALES_LEAD_ID	NUMBER;
48  X_SALES_LEAD_LINE_ID   NUMBER;
49  X_INSTANCE NUMBER;
50 
51 
52   CURSOR c_1 IS
53 	SELECT
54 	avg(nvl(vsize( ASLL.SALES_LEAD_LINE_ID ), 0)),
55 	avg(nvl(vsize( ASL.SALES_LEAD_ID), 0))
56         FROM AS_SALES_LEAD_LINES ASLL ,
57         AS_SALES_LEADS ASL ,
58         EDW_LOCAL_INSTANCE INST
59         WHERE ASLL.SALES_LEAD_ID = ASL.SALES_LEAD_ID AND
60         ( (ASL.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') )
61         OR (ASLL.LAST_UPDATE_DATE > TO_DATE('1000/01/01', 'YYYY/MM/DD') ) )
62         AND ( ASL.DELETED_FLAG IS NULL OR ASL.DELETED_FLAG <> 'Y' )
63 	AND ASL.last_update_date between
64         p_from_date  and  p_to_date ;
65 
66 
67   CURSOR c_2 IS
68 	select
69 	 avg(nvl(vsize(INSTANCE_CODE), 0))
70 	 from EDW_LOCAL_INSTANCE ;
71 
72 
73 
74   BEGIN
75 
76     dbms_output.enable(1000000);
77 
78     OPEN c_1;
79       FETCH c_1 INTO
80 	 X_SALES_LEAD_ID        ,
81 	 X_SALES_LEAD_LINE_ID   ;
82 
83     CLOSE c_1;
84 
85     x_total := 5  +
86 		ceil(	 X_SALES_LEAD_ID +1) +
87 		3*ceil(	 X_SALES_LEAD_LINE_ID +1);
88 
89 
90     OPEN c_2;
91       FETCH c_2 INTO  x_INSTANCE;
92     CLOSE c_2;
93 
94     x_total := x_total + 5*ceil(x_INSTANCE + 1);
95 
96     x_total := 3*x_total + 15*(x_constant + 1);
97 
98     -- dbms_output.put_line('     ');
99     dbms_output.put_line('The average row length is : ' || to_char(x_total));
100 
101   p_avg_row_len := x_total;
102 
103   END;  -- procedure est_row_len.
104 
105 END;  -- package body BIM_EDW_LEADS_M_SIZE