eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIM_EDW_EVENTS_M_SIZE

Source


1 PACKAGE BODY BIM_EDW_EVENTS_M_SIZE AS
2 /* $Header: bimszevb.pls 115.0 2001/03/14 12:01:57 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_event_offers_vl aeo ,
16 	ams_event_headers_all_b aeh ,
17 	edw_local_instance inst
18 	WHERE
19         aeo.event_header_id = aeh.event_header_id and aeo.event_level = 'MAIN'
20 	and ((aeo.last_update_date > to_date('1000/01/01', 'YYYY/MM/DD'))
21         or
22         (aeh.last_update_date > to_date('1000/01/01', 'YYYY/MM/DD')))
23 	and
24 	aeo.last_update_date between
25         p_from_date  and  p_to_date
26         UNION
27         select count(*)
28 	FROM ams_event_headers_vl aeh ,
29 	edw_local_instance inst
30 	WHERE
31 	aeh.event_level = 'MAIN' and
32         aeh.last_update_date between
33         p_from_date  and  p_to_date
34         );
35 
36 
37 BEGIN
38 
39   dbms_output.enable(1000000);
40 
41   OPEN c_cnt_rows;
42        FETCH c_cnt_rows INTO p_num_rows;
43   CLOSE c_cnt_rows;
44 
45     dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
46 END;  -- procedure cnt_rows.
47 
48 
49 PROCEDURE est_row_len(p_from_date DATE,
50                       p_to_date DATE,
51                       p_avg_row_len OUT NUMBER) IS
52 
53  x_date                 number := 7;
54  x_total                number := 0;
55  x_constant             number := 6;
56 
57  x_event_offer_name NUMBER;
58  x_event_offer_id NUMBER;
59  x_parent_event_offer_id NUMBER;
60  x_event_header_id NUMBER;
61  x_event_level NUMBER;
62  x_event_type_code NUMBER;
63  x_user_status_id NUMBER;
64  x_last_status_date NUMBER;
65  x_system_status_code NUMBER;
66  x_timezone_id NUMBER;
67  x_reg_waitlist_pct NUMBER;
68  x_reg_overbook_pct NUMBER;
69  x_reg_minimum_capacity NUMBER;
70  x_reg_maximum_capacity NUMBER;
71  x_reg_effective_capacity NUMBER;
72  x_pricelist_line_id NUMBER;
73  x_pricelist_header_id NUMBER;
74  x_owner_user_id NUMBER;
75  x_org_id NUMBER;
76  x_inventory_item_id NUMBER;
77  x_event_location_id NUMBER;
78  x_event_duration NUMBER;
79  x_event_delivery_method_id NUMBER;
80  x_coordinator_id NUMBER;
81  x_certification_credits NUMBER;
82  x_waitlist_action_type_code NUMBER;
83  x_stream_type_code NUMBER;
84  x_source_code NUMBER;
85  x_reg_waitlist_allowed_flag NUMBER;
86  x_reg_start_time NUMBER;
87  x_reg_required_flag NUMBER;
88  x_reg_overbook_allowed_flag NUMBER;
89  x_reg_invited_only_flag NUMBER;
90  x_reg_frozen_flag NUMBER;
91  x_reg_end_time NUMBER;
92  x_reg_charge_flag NUMBER;
93  x_priority_type_code NUMBER;
94  x_partner_flag NUMBER;
95  x_overflow_flag NUMBER;
96  x_event_start_date_time NUMBER;
97  x_event_standalone_flag NUMBER;
98  x_event_required_flag NUMBER;
99  x_event_language_code NUMBER;
100  x_event_full_flag NUMBER;
101  x_event_end_date_time NUMBER;
102  x_event_duration_uom_code NUMBER;
103  x_cert_credit_type_code NUMBER;
104  x_cancellation_reason_code NUMBER;
105  x_inbound_script_name NUMBER;
106  x_email NUMBER;
107  x_phone NUMBER;
108  x_url NUMBER;
109  x_auto_register_flag NUMBER;
110  x_event_venue_id NUMBER;
111  x_reg_start_date NUMBER;
112  x_reg_minimum_req_by_date NUMBER;
113  x_reg_end_date NUMBER;
114  x_event_start_date NUMBER;
115  x_event_end_date NUMBER;
116  x_event_mktg_message NUMBER;
117  x_description NUMBER;
118  x_creation_date NUMBER;
119  x_INSTANCE NUMBER;
120 
121 
122   CURSOR c_1 IS
123 	SELECT
124 	avg(nvl(vsize(aeo.event_offer_name), 0)),
125 	avg(nvl(vsize(aeo.event_offer_id), 0)),
126 	avg(nvl(vsize(aeo.parent_event_offer_id), 0)),
127 	avg(nvl(vsize(aeo.event_header_id), 0)),
128 	avg(nvl(vsize(aeo.event_level), 0)),
129 	avg(nvl(vsize(aeo.event_type_code), 0)),
130 	avg(nvl(vsize(aeo.user_status_id), 0)),
131 	avg(nvl(vsize(aeo.last_status_date), 0)),
132 	avg(nvl(vsize(aeo.system_status_code), 0)),
133 	avg(nvl(vsize(aeo.timezone_id), 0)),
134 	avg(nvl(vsize(aeo.reg_waitlist_pct), 0)),
135 	avg(nvl(vsize(aeo.reg_overbook_pct), 0)),
136 	avg(nvl(vsize(aeo.reg_minimum_capacity), 0)),
137 	avg(nvl(vsize(aeo.reg_maximum_capacity), 0)),
138 	avg(nvl(vsize(aeo.reg_effective_capacity), 0)),
139 	avg(nvl(vsize(aeo.pricelist_line_id), 0)),
140 	avg(nvl(vsize(aeo.pricelist_header_id), 0)),
141 	avg(nvl(vsize(aeo.owner_user_id), 0)),
142 	avg(nvl(vsize(aeo.org_id), 0)),
143 	avg(nvl(vsize(aeo.inventory_item_id), 0)),
144 	avg(nvl(vsize(aeo.event_location_id), 0)),
145 	avg(nvl(vsize(aeo.event_duration), 0)),
146 	avg(nvl(vsize(aeo.event_delivery_method_id), 0)),
147 	avg(nvl(vsize(aeo.coordinator_id), 0)),
148 	avg(nvl(vsize(aeo.certification_credits), 0)),
149 	avg(nvl(vsize(aeo.waitlist_action_type_code), 0)),
150 	avg(nvl(vsize(aeo.stream_type_code), 0)),
151 	avg(nvl(vsize(aeo.source_code), 0)),
152 	avg(nvl(vsize(aeo.reg_waitlist_allowed_flag), 0)),
153 	avg(nvl(vsize(aeo.reg_start_time), 0)),
154 	avg(nvl(vsize(aeo.reg_required_flag), 0)),
155 	avg(nvl(vsize(aeo.reg_overbook_allowed_flag), 0)),
156 	avg(nvl(vsize(aeo.reg_invited_only_flag), 0)),
157 	avg(nvl(vsize(aeo.reg_frozen_flag), 0)),
158 	avg(nvl(vsize(aeo.reg_end_time), 0)),
159 	avg(nvl(vsize(aeo.reg_charge_flag), 0)),
160 	avg(nvl(vsize(aeo.priority_type_code), 0)),
161 	avg(nvl(vsize(aeo.partner_flag), 0)),
162 	avg(nvl(vsize(aeo.overflow_flag), 0)),
163 	avg(nvl(vsize(aeo.event_start_date_time), 0)),
164 	avg(nvl(vsize(aeo.event_standalone_flag), 0)),
165 	avg(nvl(vsize(aeo.event_required_flag), 0)),
166 	avg(nvl(vsize(aeo.event_language_code), 0)),
167 	avg(nvl(vsize(aeo.event_full_flag), 0)),
168 	avg(nvl(vsize(aeo.event_end_date_time), 0)),
169 	avg(nvl(vsize(aeo.event_duration_uom_code), 0)),
170 	avg(nvl(vsize(aeo.cert_credit_type_code), 0)),
171 	avg(nvl(vsize(aeo.cancellation_reason_code), 0)),
172 	avg(nvl(vsize(aeo.inbound_script_name), 0)),
173 	avg(nvl(vsize(aeo.email), 0)),
174 	avg(nvl(vsize(aeo.phone), 0)),
175 	avg(nvl(vsize(aeo.url), 0)),
176 	avg(nvl(vsize(aeo.auto_register_flag), 0)),
177 	avg(nvl(vsize(aeo.event_venue_id), 0)),
178 	avg(nvl(vsize(aeo.reg_start_date), 0)),
179 	avg(nvl(vsize(aeo.reg_minimum_req_by_date), 0)),
180 	avg(nvl(vsize(aeo.reg_end_date), 0)),
181 	avg(nvl(vsize(aeo.event_start_date), 0)),
182 	avg(nvl(vsize(aeo.event_end_date), 0)),
183 	avg(nvl(vsize(aeo.event_mktg_message), 0)),
184 	avg(nvl(vsize(aeo.event_offer_name), 0)),
185 	avg(nvl(vsize(aeo.description), 0)),
186 	avg(nvl(vsize(aeo.creation_date), 0))
187         FROM ams_event_offers_vl aeo ,
188         ams_event_headers_all_b aeh ,
189         edw_local_instance inst
190         WHERE
191         aeo.event_header_id = aeh.event_header_id and aeo.event_level = 'MAIN'
192         and ((aeo.last_update_date > to_date('1000/01/01', 'YYYY/MM/DD'))
193         or
194         (aeh.last_update_date > to_date('1000/01/01', 'YYYY/MM/DD')));
195 
196 
197   CURSOR c_2 IS
198 	select
199 	 avg(nvl(vsize(INSTANCE_CODE), 0))
200 	 from EDW_LOCAL_INSTANCE ;
201 
202 
203 
204   BEGIN
205 
206     dbms_output.enable(1000000);
207 
208     OPEN c_1;
209       FETCH c_1 INTO
210 	 x_event_offer_name,
211 	 x_event_offer_id,
212 	 x_parent_event_offer_id,
213 	 x_event_header_id,
214 	 x_event_level,
215 	 x_event_type_code,
216 	 x_user_status_id,
217 	 x_last_status_date,
218 	 x_system_status_code,
219 	 x_timezone_id,
220 	 x_reg_waitlist_pct,
221 	 x_reg_overbook_pct,
222 	 x_reg_minimum_capacity,
223 	 x_reg_maximum_capacity,
224 	 x_reg_effective_capacity,
225 	 x_pricelist_line_id,
226 	 x_pricelist_header_id,
227 	 x_owner_user_id,
228 	 x_org_id,
229 	 x_inventory_item_id,
230 	 x_event_location_id,
231 	 x_event_duration,
232 	 x_event_delivery_method_id,
233 	 x_coordinator_id,
234 	 x_certification_credits,
235 	 x_waitlist_action_type_code,
236 	 x_stream_type_code,
237 	 x_source_code,
238 	 x_reg_waitlist_allowed_flag,
239 	 x_reg_start_time,
240 	 x_reg_required_flag,
241 	 x_reg_overbook_allowed_flag,
242 	 x_reg_invited_only_flag,
243 	 x_reg_frozen_flag,
244 	 x_reg_end_time,
245 	 x_reg_charge_flag,
246 	 x_priority_type_code,
247 	 x_partner_flag,
248 	 x_overflow_flag,
249 	 x_event_start_date_time,
250 	 x_event_standalone_flag,
251 	 x_event_required_flag,
252 	 x_event_language_code,
253 	 x_event_full_flag,
254 	 x_event_end_date_time,
255 	 x_event_duration_uom_code,
256 	 x_cert_credit_type_code,
257 	 x_cancellation_reason_code,
258 	 x_inbound_script_name,
259 	 x_email,
260 	 x_phone,
261 	 x_url,
262 	 x_auto_register_flag,
263 	 x_event_venue_id,
264 	 x_reg_start_date,
265 	 x_reg_minimum_req_by_date,
266 	 x_reg_end_date,
267 	 x_event_start_date,
268 	 x_event_end_date,
269 	 x_event_mktg_message,
270 	 x_event_offer_name,
271 	 x_description,
272 	 x_creation_date ;
273 
274     CLOSE c_1;
275 
276     x_total := 20  +
277 	ceil(x_event_offer_name+1) +
278 	ceil(x_event_offer_id+1) +
279 	ceil(x_parent_event_offer_id+1) +
280 	ceil(x_event_header_id+1) +
281 	ceil(x_event_level+1) +
282 	ceil(x_event_type_code+1) +
283 	ceil(x_user_status_id+1) +
284 	ceil(x_last_status_date+1) +
285 	ceil(x_system_status_code+1) +
286 	ceil(x_timezone_id+1) +
287 	ceil(x_reg_waitlist_pct+1) +
288 	ceil(x_reg_overbook_pct+1) +
289 	ceil(x_reg_minimum_capacity+1) +
290 	ceil(x_reg_maximum_capacity+1) +
291 	ceil(x_reg_effective_capacity+1) +
292 	ceil(x_pricelist_line_id+1) +
293 	ceil(x_pricelist_header_id+1) +
294 	ceil(x_owner_user_id+1) +
295 	ceil(x_org_id+1) +
296 	ceil(x_inventory_item_id+1) +
297 	ceil(x_event_location_id+1) +
298 	ceil(x_event_duration+1) +
299 	ceil(x_event_delivery_method_id+1) +
300 	ceil(x_coordinator_id+1) +
301 	ceil(x_certification_credits+1) +
302 	ceil(x_waitlist_action_type_code+1) +
303 	ceil(x_stream_type_code+1) +
304 	ceil(x_source_code+1) +
305 	ceil(x_reg_waitlist_allowed_flag+1) +
306 	ceil(x_reg_start_time+1) +
307 	ceil(x_reg_required_flag+1) +
308 	ceil(x_reg_overbook_allowed_flag+1) +
309 	ceil(x_reg_invited_only_flag+1) +
310 	ceil(x_reg_frozen_flag+1) +
311 	ceil(x_reg_end_time+1) +
312 	ceil(x_reg_charge_flag+1) +
313 	ceil(x_priority_type_code+1) +
314 	ceil(x_partner_flag+1) +
315 	ceil(x_overflow_flag+1) +
316 	ceil(x_event_start_date_time+1) +
317 	ceil(x_event_standalone_flag+1) +
318 	ceil(x_event_required_flag+1) +
319 	ceil(x_event_language_code+1) +
320 	ceil(x_event_full_flag+1) +
321 	ceil(x_event_end_date_time+1) +
322 	ceil(x_event_duration_uom_code+1) +
323 	ceil(x_cert_credit_type_code+1) +
324 	ceil(x_cancellation_reason_code+1) +
325 	ceil(x_inbound_script_name+1) +
326 	ceil(x_email+1) +
327 	ceil(x_phone+1) +
328 	ceil(x_url+1) +
329 	ceil(x_auto_register_flag+1) +
330 	ceil(x_event_venue_id+1) +
331 	ceil(x_reg_start_date+1) +
332 	ceil(x_reg_minimum_req_by_date+1) +
333 	ceil(x_reg_end_date+1) +
334 	ceil(x_event_start_date+1) +
335 	ceil(x_event_end_date+1) +
336 	ceil(x_event_mktg_message+1) +
337 	ceil(x_event_offer_name+1) +
338 	ceil(x_description+1) +
339 	ceil(x_creation_date+1);
340 
341 
342     OPEN c_2;
343       FETCH c_2 INTO  x_INSTANCE;
344     CLOSE c_2;
345 
346     x_total := x_total + 3*ceil(x_INSTANCE + 1);
347 
348     x_total := 2*x_total + 15*(x_constant + 1);
349 
350     -- dbms_output.put_line('     ');
351     dbms_output.put_line('The average row length is : ' || to_char(x_total));
352 
353   p_avg_row_len := x_total;
354 
355   END;  -- procedure est_row_len.
356 
357 END;  -- package body BIM_EDW_EVENTS_M_SIZE