[Home] [Help]
PACKAGE BODY: APPS.BIM_EDW_CMPGNS_M_SIZE
Source
1 PACKAGE BODY BIM_EDW_CMPGNS_M_SIZE AS
2 /* $Header: bimszcpb.pls 115.0 2001/03/14 12:01:43 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_CAMPAIGN_SCHEDULES AMS ,
16 AMS_CAMPAIGNS_VL AMC ,
17 EDW_LOCAL_INSTANCE INST
18 WHERE AMS.CAMPAIGN_ID = AMC.CAMPAIGN_ID
19 and
20 AMS.last_update_date between
21 p_from_date and p_to_date
22 );
23
24
25 BEGIN
26
27 dbms_output.enable(1000000);
28
29 OPEN c_cnt_rows;
30 FETCH c_cnt_rows INTO p_num_rows;
31 CLOSE c_cnt_rows;
32
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 x_CAMPAIGN_SCHEDULE_ID NUMBER;
46 x_CAMPAIGN_ID1 NUMBER;
47 x_CAMPAIGN_ID2 NUMBER;
48 x_SOURCE_CODE NUMBER;
49 x_FREQUENCY NUMBER;
50 x_FREQUENCY_UOM_CODE NUMBER;
51 x_DELIVERABLE_ID NUMBER;
52 x_ACTIVITY_OFFER_ID NUMBER;
53 x_FORECASTED_START_DATE_TIME NUMBER;
54 x_FORECASTED_END_DATE_TIME NUMBER;
55 x_ACTUAL_START_DATE_TIME NUMBER;
56 x_ACTUAL_END_DATE_TIME NUMBER;
57 x_CREATION_DATE NUMBER;
58 x_LAST_UPDATE_DATE NUMBER;
59 x_INSTANCE NUMBER;
60
61
62 CURSOR c_1 IS
63 SELECT
64 avg(nvl(vsize( CAMPAIGN_SCHEDULE_ID ), 0)),
65 avg(nvl(vsize( CAMPAIGN_ID), 0)),
66 avg(nvl(vsize( SOURCE_CODE), 0)),
67 avg(nvl(vsize( FREQUENCY), 0)),
68 avg(nvl(vsize( FREQUENCY_UOM_CODE), 0)),
69 avg(nvl(vsize( DELIVERABLE_ID), 0)),
70 avg(nvl(vsize( ACTIVITY_OFFER_ID), 0)),
71 avg(nvl(vsize( FORECASTED_START_DATE_TIME), 0)),
72 avg(nvl(vsize( FORECASTED_END_DATE_TIME), 0)),
73 avg(nvl(vsize( ACTUAL_START_DATE_TIME), 0)),
74 avg(nvl(vsize( ACTUAL_END_DATE_TIME), 0)),
75 avg(nvl(vsize( CREATION_DATE), 0)),
76 avg(nvl(vsize( LAST_UPDATE_DATE ), 0))
77 FROM AMS_CAMPAIGN_SCHEDULES
78 where last_update_date between
79 p_from_date and p_to_date;
80
81
82
83 CURSOR c_2 IS
84 select
85 avg(nvl(vsize(campaign_id), 0))
86 from AMS_CAMPAIGNS_VL ;
87
88
89 CURSOR c_3 IS
90 select
91 avg(nvl(vsize(INSTANCE_CODE), 0))
92 from EDW_LOCAL_INSTANCE ;
93
94
95
96 BEGIN
97
98 dbms_output.enable(1000000);
99
100 OPEN c_1;
101 FETCH c_1 INTO
102 x_CAMPAIGN_SCHEDULE_ID,
103 x_CAMPAIGN_ID1,
104 x_SOURCE_CODE,
105 x_FREQUENCY,
106 x_FREQUENCY_UOM_CODE,
107 x_DELIVERABLE_ID,
108 x_ACTIVITY_OFFER_ID,
109 x_FORECASTED_START_DATE_TIME,
110 x_FORECASTED_END_DATE_TIME,
111 x_ACTUAL_START_DATE_TIME,
112 x_ACTUAL_END_DATE_TIME,
113 x_CREATION_DATE,
114 x_LAST_UPDATE_DATE;
115
116 CLOSE c_1;
117
118 x_total := 35 +
119 ceil( x_CAMPAIGN_SCHEDULE_ID +1) +
120 ceil( x_CAMPAIGN_ID1 +1) +
121 ceil( x_SOURCE_CODE +1) +
122 ceil( x_FREQUENCY +1) +
123 ceil( x_FREQUENCY_UOM_CODE +1) +
124 ceil( x_DELIVERABLE_ID +1) +
125 ceil( x_ACTIVITY_OFFER_ID +1) +
126 ceil( x_FORECASTED_START_DATE_TIME +1) +
127 ceil( x_FORECASTED_END_DATE_TIME +1) +
128 ceil( x_ACTUAL_START_DATE_TIME +1) +
129 ceil( x_ACTUAL_END_DATE_TIME +1) +
130 ceil( x_CREATION_DATE +1) +
131 ceil( x_LAST_UPDATE_DATE +1);
132
133
134
135
136
137 OPEN c_2;
138 FETCH c_2 INTO x_CAMPAIGN_ID2;
139 CLOSE c_2;
140 x_total := x_total + ceil(x_CAMPAIGN_ID2 + 1);
141
142 OPEN c_3;
143 FETCH c_3 INTO x_INSTANCE;
144 CLOSE c_3;
145
146 x_total := x_total + 3*ceil(x_INSTANCE + 1);
147
148 x_total := 10*x_total + 15*(x_constant + 1);
149
150 -- dbms_output.put_line(' ');
151 dbms_output.put_line('The average row length is : ' || to_char(x_total));
152
153 p_avg_row_len := x_total;
154
155 END; -- procedure est_row_len.
156
157 END; -- package body BIM_EDW_CMPGNS_M_SIZE