PACKAGE BODY: APPS.BIM_EDW_IH_MEDIA_M_SIZE

Source

1 PACKAGE BODY BIM_EDW_IH_MEDIA_M_SIZE AS
2 /* $Header: bimszmeb.pls 115.0 2001/03/14 12:03:06 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_MEDIA_ITEMS JMI ,
16 	FND_LOOKUP_VALUES_VL LKP ,
17 	EDW_LOCAL_INSTANCE INST
18 	WHERE JMI.MEDIA_ITEM_TYPE = LKP.LOOKUP_CODE AND
19 	LKP.LOOKUP_TYPE = 'JTF_MEDIA_TYPE' AND LKP.VIEW_APPLICATION_ID = 0
20         AND
21         LKP.SECURITY_GROUP_ID=FND_GLOBAL.LOOKUP_SECURITY_GROUP( LKP.LOOKUP_CODE,
22         LKP.VIEW_APPLICATION_ID )
23         and jmi.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_DIRECTION NUMBER;
48 
49 
50  x_INSTANCE NUMBER;
51 
52 
53   CURSOR c_1 IS
54 	SELECT
55 	avg(nvl(vsize(JMI.DIRECTION ), 0))
56         FROM JTF_IH_MEDIA_ITEMS JMI
57         where jmi.last_update_date between
58         p_from_date  and  p_to_date;
59 
60 
61   CURSOR c_2 IS
62 	select
63 	 avg(nvl(vsize(INSTANCE_CODE), 0))
64 	 from EDW_LOCAL_INSTANCE ;
65 
66 
67 
68   BEGIN
69 
70     dbms_output.enable(1000000);
71 
72     OPEN c_1;
73       FETCH c_1 INTO
74 	 X_DIRECTION;
75 
76     CLOSE c_1;
77 
78     x_total := 150  +
79 	 6*ceil(X_DIRECTION + 1);
80 
81     OPEN c_2;
82       FETCH c_2 INTO  x_INSTANCE;
83     CLOSE c_2;
84 
85     x_total := x_total + 2*ceil(x_INSTANCE + 1);
86 
87     x_total := x_total + 15*(x_constant + 1);
88 
89     -- dbms_output.put_line('     ');
90     dbms_output.put_line('The average row length is : ' || to_char(x_total));
91 
92   p_avg_row_len := x_total;
93 
94   END;  -- procedure est_row_len.
95 
96 END;  -- package body BIM_EDW_IH_MEDIA_M_SIZE