PACKAGE BODY: APPS.BIM_EDW_INTRCTNS_F_SIZE

Source

1 PACKAGE BODY BIM_EDW_INTRCTNS_F_SIZE AS
2 /* $Header: bimszfib.pls 115.0 2001/03/14 12:02: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 JTF_IH_ACTIVITIES JIA,
16 	 JTF_IH_INTERACTIONS JII,
17 	 HZ_PARTY_SITES HPS,
18 	 EDW_BIM_SOURCE_CODE_DETAILS BSCD,
19 	 BIM_EDW_INTRCTNS_INC INC,
20 	 EDW_LOCAL_INSTANCE INST
21         WHERE JIA.INTERACTION_ID = JII.INTERACTION_ID
22          AND JII.PARTY_ID = HPS.PARTY_ID (+)
23 	 AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
24          AND JIA.ACTIVITY_ID = INC.PRIMARY_KEY
25 	 AND NVL(JIA.SOURCE_CODE_ID, -999) = BSCD.SOURCE_CODE_ID
26 	 and
27 	 JIA.last_update_date between
28          p_from_date  and  p_to_date
29         );
30 
31 BEGIN
32 
33   dbms_output.enable(1000000);
34 
35   OPEN c_cnt_rows;
36        FETCH c_cnt_rows INTO p_num_rows;
37   CLOSE c_cnt_rows;
38 
39 
40     dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
41 END;  -- procedure cnt_rows.
42 
43 
44 PROCEDURE est_row_len(p_from_date DATE,
45                       p_to_date DATE,
46                       p_avg_row_len OUT NUMBER) IS
47 
48  x_date                 number := 7;
49  x_total                number := 0;
50  x_constant             number := 6;
51  x_ACTIVITY_ID NUMBER;
52  x_INTERACTION_ID NUMBER;
53  x_DURATION NUMBER;
54  x_SOURCE_CODE NUMBER;
55 
56  x_CAMPAIGN_FK NUMBER;
57  x_EVENT_FK NUMBER;
58  x_MEDIA_CHANNEL_FK NUMBER;
59  x_OFFER_FK NUMBER;
60 
61  x_INSTANCE NUMBER;
62 
63 
64   CURSOR c_1 IS
65 	SELECT
66 	avg(nvl(vsize( ACTIVITY_ID ), 0)),
67 	avg(nvl(vsize( INTERACTION_ID), 0)),
68 	avg(nvl(vsize( DURATION), 0)),
69 	avg(nvl(vsize( SOURCE_CODE), 0))
70 	FROM JTF_IH_ACTIVITIES
71         where last_update_date between
72         p_from_date  and  p_to_date;
73 
74 
75 
76   CURSOR c_2 IS
77 	select
78 	 avg(nvl(vsize(CAMPAIGN_FK), 0)),
79 	 avg(nvl(vsize(EVENT_FK), 0)),
80 	 avg(nvl(vsize(MEDIA_CHANNEL_FK), 0)),
81 	 avg(nvl(vsize(OFFER_FK), 0))
82 	 from EDW_BIM_SOURCE_CODE_DETAILS  ;
83 
84 
85   CURSOR c_3 IS
86 	select
87 	 avg(nvl(vsize(INSTANCE_CODE), 0))
88 	 from EDW_LOCAL_INSTANCE ;
89 
90 
91 
92   BEGIN
93 
94     dbms_output.enable(1000000);
95 
96     OPEN c_1;
97       FETCH c_1 INTO
98 	 x_ACTIVITY_ID,
99 	 x_INTERACTION_ID,
100 	 x_DURATION,
101 	 x_SOURCE_CODE;
102 
103     CLOSE c_1;
104 
105     x_total := 160  +
106 		ceil(	 x_ACTIVITY_ID +1) +
107 		ceil(	 x_INTERACTION_ID +1) +
108 		ceil(	 x_DURATION +1) +
109 		ceil(	 x_SOURCE_CODE +1) ;
110 
111 
112 
113 
114     OPEN c_2;
115       FETCH c_2 INTO
116 	 x_CAMPAIGN_FK,
117 	 x_EVENT_FK,
118 	 x_MEDIA_CHANNEL_FK,
119 	 x_OFFER_FK;
120     CLOSE c_2;
121     x_total := x_total +
122 	ceil(x_CAMPAIGN_FK + 1) +
123 	ceil(x_EVENT_FK + 1) +
124 	ceil(x_MEDIA_CHANNEL_FK + 1) +
125 	ceil(x_OFFER_FK + 1);
126 
127     OPEN c_3;
128       FETCH c_3 INTO  x_INSTANCE;
129     CLOSE c_3;
130 
131     x_total := x_total + 8*ceil(x_INSTANCE + 1);
132 
133     x_total := x_total + 15*(x_constant + 1);
134 
135     -- dbms_output.put_line('     ');
136     dbms_output.put_line('The average row length is : ' || to_char(x_total));
137   p_avg_row_len := x_total;
138 
139   END;  -- procedure est_row_len.
140 
141 END;  -- package body BIM_EDW_INTRCTNS_F_SIZE