1 PACKAGE BODY MTH_SUSTAIN_ASPECT_PKG AS
2 /*$Header: mthesatb.pls 120.4 2010/12/07 00:22:18 gtippire noship $ */
3
4 p_reading MeterReadingTable;
5 p_shift EntityShiftTable;
6
7
8 /* ****************************************************************************
9 * Function :IS_RAW_DATA_ROW_VALID *
10 * Description :Check if the raw from MTH_TAG_METER_READINGS_RAW is *
11 * valid or not. If it violates any validation rules, *
12 * return -1; otherwise, return 1 *
13 * File Name :MTHSUSAB.PLS *
14 * Visibility :Private *
15 * Parameters :p_tag_code - Tag code *
16 * p_reading_time - Reading time *
17 * p_tag_value - tag value *
18 * p_is_number - 1 if tag value is number; 0 otherwise *
19 * p_is_cumulative - 1 to apply incremental logic; *
20 * 0 otherwise *
21 * p_is_assending - 1 if tag is assending order; *
22 * 0 otherwise *
23 * p_initial_value - Tag initial value *
24 * p_max_reset_value - *
25 * p_num_meters - Number of meters associated with tag *
26 * p_prev_reading_time - reading time for the previous *
27 * tag reading *
28 * OUT parameters: *
29 * p_is_valid_timestamp - Whether the reading time in *
30 * in the current entry can be used to calculate the *
31 * from time of the enxt reading. *
32 * Return Value : Found violations of the following rules: *
33 * 'NGV' - Usage value is negative. *
34 * 'OTR' - Usage value is out of range defined *
35 * for a cumulative tag. *
36 * 'OTO' - The raw reading data is out of order. *
37 * 'DUP' - The raw reading data is duplicated. *
38 * 'MIM' - There is no meter associated with the tag *
39 * 'IDS' - The data source is not for SUSTAINABILITY *
43 **************************************************************************** */
40 * 'WFQ' - Wrong frequency number *
41 * 'FTD' - Data at the Future time *
42 * NULL - Valid row *
44
45 FUNCTION IS_RAW_DATA_ROW_VALID
46 (p_tag_code IN VARCHAR2,
47 p_reading_time IN DATE,
48 p_tag_value IN NUMBER,
49 p_is_number IN NUMBER,
50 p_is_cumulative IN NUMBER,
51 p_is_assending IN NUMBER,
52 p_initial_value IN NUMBER,
53 p_max_reset_value IN NUMBER,
54 p_tag_type IN varchar2,
55 p_frequency IN NUMBER,
56 p_num_meters IN NUMBER,
57 p_prev_reading_time IN DATE,
58 p_is_valid_timestamp OUT NOCOPY BOOLEAN) RETURN VARCHAR2
59 IS
60 v_is_valid NUMBER;
61 v_err_code VARCHAR2(255) := '';
62 BEGIN
63 p_is_valid_timestamp := FALSE;
64 -- Test the negative case first since other error cases
65 -- do not write information into the LATEST table.
66 IF (p_tag_value < 0) THEN
67 v_err_code := v_err_code || 'NGV ';
68 IF p_is_cumulative = 0 THEN
69 p_is_valid_timestamp := TRUE;
70 END IF;
71 END IF;
72 IF (p_tag_type IS NULL or p_tag_type <> 'SUSTAINABILITY') THEN
73 v_err_code := v_err_code || 'IDS ';
74 END IF;
75 IF (p_is_cumulative = 0 AND
76 p_frequency <= 0) THEN
77 v_err_code := v_err_code || 'WFQ ';
78 p_is_valid_timestamp := FALSE;
79 END IF;
80 IF (p_is_number = 1 AND p_is_cumulative = 1 AND
81 p_tag_value > p_max_reset_value) THEN
82 v_err_code := v_err_code || 'OTR ';
83 p_is_valid_timestamp := FALSE;
84 END IF;
85 IF (p_prev_reading_time IS NOT NULL AND
86 p_reading_time < p_prev_reading_time) THEN
87 v_err_code := v_err_code || 'OTO ';
88 p_is_valid_timestamp := FALSE;
89 END IF;
90 IF (p_prev_reading_time IS NOT NULL AND
91 p_reading_time = p_prev_reading_time) THEN
92 v_err_code := v_err_code || 'DUP ';
93 p_is_valid_timestamp := FALSE;
94 END IF;
95 IF (p_reading_time > SYSDATE) THEN
96 v_err_code := v_err_code || 'FTD ';
97 p_is_valid_timestamp := FALSE;
98 END IF;
99 IF (p_num_meters = 0) THEN
100 v_err_code := v_err_code || 'MIM ';
101 p_is_valid_timestamp := FALSE;
102 END IF;
103 IF (Length(v_err_code) = 0) THEN
104 v_err_code := NULL;
105 p_is_valid_timestamp := TRUE;
106 END IF;
107 RETURN v_err_code;
108 END IS_RAW_DATA_ROW_VALID;
109
110
111
112
113 /* ****************************************************************************
114 * Procedure :insert_row_to_err_tab *
115 * Description :Insert the error row into the error with error code *
116 * File Name :MTHSUSAB.PLS *
117 * Visibility :Private *
118 * Parameters :p_tag_code - Tag code *
119 * p_reading_time - Reading time *
120 * p_tag_value - tag value *
121 * p_err_code - Error codes *
122 * Return Value :None *
123 **************************************************************************** */
124 PROCEDURE insert_row_to_err_tab(P_TAG_CODE IN VARCHAR2,
125 P_READING_TIME IN DATE,
126 P_TAG_VALUE IN NUMBER,
127 P_ERROR_CODE IN VARCHAR2)
128 IS
129 v_reprocess_ready_yn VARCHAR2(1) := 'N';
130 v_meter_reading_err_pk_key NUMBER;
131 BEGIN
132 INSERT INTO MTH_METER_READINGS_ERR
133 (METER_READINGS_ERR_PK_KEY, TO_TIME, USAGE_VALUE, TAG_CODE,
134 REPROCESS_READY_YN, ERR_CODE)
135 VALUES (MTH_METER_READINGS_ERR_S.NEXTVAL, P_READING_TIME,
136 P_TAG_VALUE, P_TAG_CODE, v_reprocess_ready_yn, P_ERROR_CODE);
137
138 EXCEPTION
139 WHEN others THEN
140 NULL; -- Skip the error in this case
141
142 END insert_row_to_err_tab;
143
144
145
146 /* ****************************************************************************
147 * Function :get_incremental_value *
148 * Description :Get the incremental value for a tag value *
149 * File Name :MTHSUSAB.PLS *
150 * Visibility :Private *
151 * Parameters :p_tag_value - tag value *
152 * p_is_number - 1 if tag value is number; 0 otherwise *
153 * p_is_cumulative - 1 to apply incremental logic; *
154 * 0 otherwise *
155 * p_is_assending - 1 if tag is assending order; *
156 * 0 otherwise *
157 * p_initial_value - Tag initial value *
158 * p_max_reset_value - *
159 * p_prev_tag_value - Previous tag value *
160 * Return Value :Incremental value if incremental logic needs to be *
161 * be applied; return p_tag_value otherwise *
162 **************************************************************************** */
163
164 FUNCTION get_incremental_value(P_TAG_VALUE IN NUMBER,
165 P_IS_NUMBER IN NUMBER,
169 P_MAX_RESET_VALUE IN NUMBER,
166 P_IS_CUMULATIVE IN NUMBER,
167 P_IS_ASSENDING IN NUMBER,
168 P_INITIAL_VALUE IN NUMBER,
170 p_prev_tag_value IN NUMBER) RETURN NUMBER
171 IS
172 v_incr_value NUMBER;
173 BEGIN
174 -- 1. Do not need to apply the incremental logic
175 IF (P_IS_NUMBER IS NULL OR P_IS_NUMBER <> 1 OR
176 P_IS_CUMULATIVE IS NULL OR P_IS_CUMULATIVE <> 1) THEN
177 v_incr_value := P_TAG_VALUE;
178 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 1 AND
179 p_prev_tag_value IS NOT NULL) THEN
180 -- 2. Assending tag and it is not the first reding
181 -- 2.1 not reset
182 v_incr_value := CASE WHEN P_TAG_VALUE >= p_prev_tag_value
183 THEN P_TAG_VALUE - p_prev_tag_value
184 -- 2.2 after reset
185 ELSE P_MAX_RESET_VALUE - p_prev_tag_value +
186 P_TAG_VALUE
187 END;
188 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 1 AND
189 p_prev_tag_value IS NULL) THEN
190 -- 3. Assending tag and it is the first reding
191 -- 3.1 First reading
192 v_incr_value := CASE WHEN P_TAG_VALUE >= P_INITIAL_VALUE
193 THEN P_TAG_VALUE - P_INITIAL_VALUE
194 -- 3.2 First reading but reset already
195 ELSE P_MAX_RESET_VALUE - P_INITIAL_VALUE + P_TAG_VALUE
196 END;
197 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 0 AND
198 p_prev_tag_value IS NOT NULL) THEN
199 -- 4. Descending tag and it is not the first reding
200 -- 4.1 not reset
201 v_incr_value := CASE WHEN P_TAG_VALUE <= p_prev_tag_value
202 THEN p_prev_tag_value - P_TAG_VALUE
203 -- 2.2 after reset
204 ELSE p_prev_tag_value + P_MAX_RESET_VALUE -
205 P_TAG_VALUE
206 END;
207 ELSIF (P_IS_CUMULATIVE = 1 AND P_IS_ASSENDING = 0 AND
208 p_prev_tag_value IS NULL) THEN
209 -- 5. Descending tag and it is the first reding
210 -- 4.1 not reset
211 v_incr_value := CASE WHEN P_TAG_VALUE <= P_INITIAL_VALUE
212 THEN P_INITIAL_VALUE - P_TAG_VALUE
213 -- 3.2 First reading but reset already
214 ELSE P_INITIAL_VALUE + P_MAX_RESET_VALUE - P_TAG_VALUE
215 END;
216 END IF;
217 RETURN v_incr_value;
218
219 END get_incremental_value;
220
221
222 /* ****************************************************************************
223 * Procedure :insert_runtime_error *
224 * Description :Insert runtime exception into mth_runtime_err table *
225 * File Name :MTHSUSAB.PLS *
226 * Visibility :Private *
227 * Parameters :p_proc_func_name - Name of the calling procedure or *
228 * function *
229 * p_error_code - Oracle error code *
230 * p_error_msg - error message *
231 * Return Value :None *
232 **************************************************************************** */
233 PROCEDURE insert_runtime_error(p_proc_func_name IN VARCHAR2,
234 p_error_code IN NUMBER,
235 p_error_msg IN VARCHAR2)
236 IS
237 v_module_name VARCHAR2(80);
238 BEGIN
239 v_module_name := 'MTH_SUSTAIN_ASPECT_PKG.' || p_proc_func_name;
240 INSERT INTO mth_runtime_err
241 ( MODULE, error_code, error_msg, timestamp) VALUES
242 (v_module_name, p_error_code, p_error_msg, SYSDATE);
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 ROLLBACK;
247 END insert_runtime_error;
248
249 /* ****************************************************************************
250 * Procedure :insert_act_meters_to_readings *
251 * Description :Insert the actual meters associated with that tag *
252 * into meter readings table *
253 * File Name :MTHSUSAB.PLS *
254 * Visibility :Private *
255 * Parameters :p_tag_code - tag code *
256 * p_reading_time - tag reading time *
257 * p_incr_tag_value - incremental tag reading *
258 * p_prev_reading_time - reading time for previous one *
259 * p_frequency - frequeycy ; null if not incremental *
260 * p_meter_keys_arr - List of meter fk keys associated *
261 * with the tag *
262 * Return Value :None *
263 **************************************************************************** */
264
265 PROCEDURE insert_act_meters_to_readings(p_tag_code IN VARCHAR2,
266 p_reading_time IN DATE,
267 p_incr_tag_value IN NUMBER,
268 p_prev_reading_time IN DATE,
269 p_frequency IN NUMBER,
270 p_meter_keys_arr IN DBMS_SQL.NUMBER_TABLE)
271 IS
272 v_from_time DATE;
273 v_processed_flag VARCHAR2(1) := 'N';
277 BEGIN
274 v_system_id NUMBER := -99999;
275 v_from_time_using_frequency DATE;
276
278 v_from_time_using_frequency := p_reading_time - p_frequency + 1/86400;
279 v_from_time := CASE WHEN p_prev_reading_time IS NULL THEN p_reading_time
280 ELSE p_prev_reading_time + 1/86400
281 END;
282
283 -- Use freqnecy when available if it is the first reading or
284 -- the reading is delayed
285 IF v_from_time_using_frequency IS NOT NULL AND
286 p_prev_reading_time IS NOT NULL AND
287 v_from_time_using_frequency > v_from_time OR
288 p_prev_reading_time IS NULL AND
289 v_from_time_using_frequency IS NOT NULL THEN
290 v_from_time := v_from_time_using_frequency;
291 END IF;
292
293 IF (p_meter_keys_arr IS NOT NULL) THEN
294 FORALL i IN 1..p_meter_keys_arr.Count
295 INSERT INTO MTH_METER_READINGS
296 (METER_FK_KEY, FROM_TIME, TO_TIME, USAGE_VALUE, PROCESSED_FLAG,
297 CREATION_DATE, LAST_UPDATE_DATE, CREATION_SYSTEM_ID,
298 LAST_UPDATE_SYSTEM_ID) VALUES
299 (p_meter_keys_arr(i), v_from_time, p_reading_time,
300 p_incr_tag_value, v_processed_flag, SYSDATE, SYSDATE,
301 v_system_id, v_system_id);
302 END IF;
303
304 END insert_act_meters_to_readings;
305
306
307
308 /* ****************************************************************************
309 * Procedure :upsert_tag_to_latest_tab *
310 * Description :Update the latest reading time and tag value *
311 * for a tag if table MTH_TAG_METER_READINGS_LATEST already *
312 * has a entry for the tag. Otherwise, insert a new row *
313 * File Name :MTHSUSAB.PLS *
314 * Visibility :Private *
315 * Parameters :p_tag_code - tag code *
316 * p_latest_reading_time - reading time of the latest *
317 * p_latest_tag_value - latest tag reading *
318 * p_lookup_entry_exist - whether the entry with the *
319 * same tag code exists in the *
320 * MTH_TAG_METER_READINGS_LATEST or not *
321 * Return Value :None *
322 **************************************************************************** */
323
324 PROCEDURE upsert_tag_to_latest_tab(p_tag_code IN VARCHAR2,
325 p_latest_reading_time IN DATE,
326 p_latest_tag_value IN NUMBER,
327 p_lookup_entry_exist IN BOOLEAN)
328 IS
329 BEGIN
330 -- If the entry exists, do the update; otherwise, do the insert
331 IF (p_lookup_entry_exist) THEN
332 UPDATE MTH_TAG_METER_READINGS_LATEST
333 SET reading_time = p_latest_reading_time, tag_value = p_latest_tag_value
334 WHERE tag_code = p_tag_code;
335 ELSE
336 INSERT INTO MTH_TAG_METER_READINGS_LATEST
337 (TAG_CODE, READING_TIME, TAG_VALUE) VALUES
338 (p_tag_code, p_latest_reading_time, p_latest_tag_value);
339 END IF;
340
341 END upsert_tag_to_latest_tab;
342
343
344 /* ****************************************************************************
345 * Procedure :LOAD_ACT_METER_RAW_TO_READINGS *
346 * Description :Load data from tag meter raw for energy consumption *
347 * in MTH_TAG_METER_READINGS_RAW into meter readings table MTH_METER_READINGS *
348 * for actual meters *
349 **************************************************************************** */
350
351 PROCEDURE LOAD_ACT_METER_RAW_TO_READINGS(p_curr_partition IN NUMBER)
352 IS
353 -- TYPE DBMS_SQL.NUMBER_TABLE IS TABLE OF NUMBER;
354
355
356 -- Fetch raw data for active tags from the patition
357 -- ordered by TAG_CODE, READING_TIME
358 CURSOR c_getRawData (p_processing_flag IN NUMBER) IS
359 SELECT R.TAG_CODE, R.READING_TIME, R.TAG_VALUE,
360 Decode(DATA_TYPE, 'NUM', 1, 0) IS_NUMBER,
361 CASE WHEN Nvl(S.APPLY_INCREMENTAL_LOGIC, 'N') = 'Y' AND
362 T.READING_TYPE = 'CHNG'
363 THEN 1
364 ELSE 0
365 END AS IS_CUMULATIVE,
366 Decode(T.ORDER_TYPE, 'ASC', 1, 0) IS_ASSENDING,
367 T.INITIAL_VALUE, T.MAX_RESET_VALUE, S.TAG_TYPE,
368 T.FREQUENCY_IN_MINUTES / 1440 as FREQUENCY
369 FROM MTH_TAG_METER_READINGS_RAW R, MTH_TAG_MASTER T,
370 MTH_TAG_DATA_SOURCES S
371 WHERE
372 R.TAG_CODE = T.TAG_CODE (+) AND
373 'ACTIVE' = T.STATUS (+) AND
374 T.TAG_DATA_SOURCE_FK_KEY = S.TAG_DATA_SOURCE_PK_KEY (+) AND
375 R.PROCESSING_FLAG = p_processing_flag
376 ORDER BY TAG_CODE, READING_TIME;
377
378
379 -- Fetch actual meter keys for the given tag code
380 CURSOR c_getMetersForTag (p_tag_code IN VARCHAR2) IS
381 SELECT METER_PK_KEY
382 FROM MTH_METERS
383 WHERE TAG_CODE = p_tag_code AND meter_type = 'ACT';
384
385 -- Fetch the previous reading time for the given tag code
386 CURSOR c_getPrevReadingTimeForTag (p_tag_code IN VARCHAR2) IS
387 SELECT TAG_VALUE, READING_TIME
388 FROM MTH_TAG_METER_READINGS_LATEST
389 WHERE TAG_CODE = p_tag_code;
390
391
392 v_raw_tab_name VARCHAR2(30) := 'MTH_TAG_METER_READINGS_RAW';
393 v_curr_partition NUMBER := p_curr_partition;
397 v_err_code VARCHAR2(255);
394 v_prev_tag_code VARCHAR2(255) := NULL;
395 v_curr_tag_code VARCHAR2(255) := NULL;
396 v_meter_fk_key_array DBMS_SQL.NUMBER_TABLE;
398 v_lookup_entry_exist boolean;
399 v_prev_reading_time DATE := NULL;
400 v_prev_tag_value NUMBER := NULL;
401 v_incr_tag_value NUMBER;
402 -- Record the number of insert and update operations
403 v_num_insert_update NUMBER := 0;
404 v_reprocess_ready_yn VARCHAR2(1) := 'N';
405 v_meter_reading_err_pk_key NUMBER;
406 v_error_code NUMBER := NULL;
407 v_error_msg VARCHAR2(4000);
408 v_is_valid_timestamp BOOLEAN := FALSE;
409
410 -- Keep the information of the last reading to be used to update LATEST table
411 -- after the LOOP for the latest valid timestamp of the last tag code
412 v_last_tag_code VARCHAR2(255) := NULL;
413 v_last_reading_time DATE := NULL;
414 v_last_tag_value NUMBER := NULL;
415
416 BEGIN
417 -- 1. First switch the partition for the meter readings raw table
418 --mth_util_pkg.switch_column_default_value(v_raw_tab_name, v_curr_partition);
419 IF (v_curr_partition = 0) THEN
420 -- No data available in the table to be processed
421 RETURN;
422 END IF;
423
424 -- 2. Fetch the raw data for active tag and process each row
425 FOR r_raw_data IN c_getRawData(v_curr_partition) LOOP
426 v_curr_tag_code := r_raw_data.TAG_CODE;
427
428 -- 2.0 Update/Create entry in MTH_TAG_METER_READINGS_LATEST for previous tag
429 -- that is stored in v_last_... .
430 -- only if the there is no error or the timestamp is valid even though
431 -- there is an error in the preivoud set of values kept in v_last....
432 IF (v_last_tag_code IS NOT NULL AND v_last_tag_code <> v_curr_tag_code) THEN
433 upsert_tag_to_latest_tab(v_last_tag_code,
434 v_last_reading_time,
435 v_last_tag_value,
436 v_lookup_entry_exist);
437 v_num_insert_update := v_num_insert_update + 1;
438
439 -- Erase the information for the last record since it has been saved
440 -- into the LATEST table already.
441 v_last_tag_code := NULL;
442 v_last_reading_time := NULL;
443 v_last_tag_value := NULL;
444 END IF;
445
446 -- 2.1 Find the meters and latest reading for the new tag code
447 IF (v_prev_tag_code IS NULL OR v_prev_tag_code <> v_curr_tag_code) THEN
448 -- 2.1.0 Reset the previous reading for the new tag code
449 v_prev_tag_value := NULL;
450 v_prev_reading_time := NULL;
451
452 -- 2.1.1 Fetch meter keys for the specified tag code
453 OPEN c_getMetersForTag(v_curr_tag_code);
454 FETCH c_getMetersForTag BULK COLLECT INTO v_meter_fk_key_array;
455 CLOSE c_getMetersForTag;
456
457 -- 2.1.2 Find previous reading time and tag value for the currenttag
458 -- in the lookup table MTH_TAG_METER_READINGS_LATEST
459 OPEN c_getPrevReadingTimeForTag(v_curr_tag_code);
460 FETCH c_getPrevReadingTimeForTag INTO
461 v_prev_tag_value, v_prev_reading_time;
462 CLOSE c_getPrevReadingTimeForTag;
463 v_lookup_entry_exist := v_prev_reading_time IS NOT NULL;
464 END IF;
465
466
467
468 -- 2.2 Validate the raw data
469 v_err_code := IS_RAW_DATA_ROW_VALID (r_raw_data.TAG_CODE,
470 r_raw_data.READING_TIME,
471 r_raw_data.TAG_VALUE,
472 r_raw_data.IS_NUMBER,
473 r_raw_data.IS_CUMULATIVE,
474 r_raw_data.IS_ASSENDING,
475 r_raw_data.INITIAL_VALUE,
476 r_raw_data.MAX_RESET_VALUE,
477 r_raw_data.TAG_TYPE,
478 r_raw_data.FREQUENCY,
479 v_meter_fk_key_array.Count,
480 v_prev_reading_time,
481 v_is_valid_timestamp);
482
483 -- 2.3 Insert data into either meter readings or error table
484 IF (v_err_code IS NOT NULL OR Length(v_err_code) > 0) THEN
485 -- 2.3.1 Insert the error row to error table if there is any error
486 INSERT INTO MTH_METER_READINGS_ERR
487 (METER_READINGS_ERR_PK_KEY, TO_TIME, USAGE_VALUE, TAG_CODE,
488 REPROCESS_READY_YN, ERR_CODE)
489 VALUES (MTH_METER_READINGS_ERR_S.NEXTVAL, r_raw_data.READING_TIME,
490 r_raw_data.TAG_VALUE, r_raw_data.TAG_CODE,
491 v_reprocess_ready_yn, v_err_code);
492 v_num_insert_update := v_num_insert_update + 1;
493 ELSE
494 -- 2.3.2 Get the incremental value
495 v_incr_tag_value := get_incremental_value(r_raw_data.TAG_VALUE,
496 r_raw_data.IS_NUMBER,
497 r_raw_data.IS_CUMULATIVE,
498 r_raw_data.IS_ASSENDING,
499 r_raw_data.INITIAL_VALUE,
500 r_raw_data.MAX_RESET_VALUE,
501 v_prev_tag_value);
502
503 -- 2.3.3 Insert the data into the meter readings table
504 insert_act_meters_to_readings(r_raw_data.TAG_CODE,
505 r_raw_data.READING_TIME,
506 v_incr_tag_value,
507 v_prev_reading_time,
508 CASE r_raw_data.IS_CUMULATIVE
509 WHEN 0 THEN r_raw_data.FREQUENCY
510 ELSE NULL END,
514 END IF;
511 v_meter_fk_key_array);
512
513 v_num_insert_update := v_num_insert_update + v_meter_fk_key_array.Count;
515
516 -- 2.4 Save the current data as previous data, which can be used for :
517 -- - Use the previous reading time - 1 second as FROM TIME for new data
518 -- - Use the previous tag value to calcuate the incremental value
519 -- - Save all of above into lookup table MTH_TAG_METER_READINGS_LATEST
520 -- when processing the last reading for this tag, wihch can be
521 -- identified by find the next and different tag code
522 IF (v_err_code IS NULL OR Length(v_err_code) = 0) or
523 Length(v_err_code) > 0 and v_is_valid_timestamp THEN
524 v_prev_tag_code := v_curr_tag_code;
525 v_prev_tag_value := r_raw_data.TAG_VALUE;
526 v_prev_reading_time := Greatest(r_raw_data.READING_TIME,
527 Nvl(v_prev_reading_time,
528 r_raw_data.READING_TIME));
529
530 -- Update the information for the last reading to update LATEST table
531 -- for step 2.6
532 v_last_tag_code := v_prev_tag_code;
533 v_last_reading_time := v_prev_reading_time;
534 v_last_tag_value := v_prev_tag_value;
535 END IF;
536
537 END LOOP;
538
539 -- 2.6 Update/Create entry in MTH_TAG_METER_READINGS_LATEST for the last tag.
540 -- Since we have kept the information for the last reading, just
541 -- need to make sure that v_last_tag_code exists.
542 IF (v_last_tag_code is not NULL) THEN
543 upsert_tag_to_latest_tab(v_last_tag_code,
544 v_last_reading_time,
545 v_last_tag_value,
546 v_lookup_entry_exist);
547 END IF;
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 ROLLBACK;
552 v_error_code := SQLCODE;
553 v_error_msg := SQLERRM;
554 insert_runtime_error('insert_act_meters_to_readings',
555 v_error_code,
556 v_error_msg);
557 COMMIT;
558 RAISE;
559
560 END LOAD_ACT_METER_RAW_TO_READINGS;
561
562
563
564
565 /* ****************************************************************************
566 * Function :getEquipPowerRating *
567 * Description :Get the equipment power rating. If it is not an equipment, *
568 * return 0. *
569 * File Name :MTHSUSAB.PLS *
570 * Visibility :Private *
571 * Parameters :p_entity_fk_key - type of the entity *
572 * p_entity_type - the ending point of time to *
573 * Return Value :Power rating of the equipment. If it is not an *
574 equipment, return 0. *
575 **************************************************************************** */
576
577 FUNCTION getEquipPowerRating(p_entity_fk_key IN NUMBER,
578 p_entity_type IN VARCHAR2) RETURN NUMBER
579 IS
580 v_power_rating NUMBER := 0;
581 BEGIN
582 IF (p_entity_type = 'EQUIPMENT') THEN
583 SELECT power_rating INTO v_power_rating
584 FROM mth_equipments_d
585 WHERE equipment_pk_key = p_entity_fk_key;
586 END IF;
587 IF (v_power_rating IS NULL) THEN
588 v_power_rating := 0;
589 END IF;
590 RETURN v_power_rating;
591
592 EXCEPTION --generic exception to capture all the errors
593 -- TO DO: Need to report the error
594 WHEN others THEN
595 RETURN 0;
596 END getEquipPowerRating;
597
598
599 /* ****************************************************************************
600 * Function :getSFTInOneHour *
601 * Description :Get shift availability time in the specified hour for the *
602 * equipment power rating. If it is not an equipment, return 0*
603 * File Name :MTHSUSAB.PLS *
604 * Visibility :Private *
605 * Parameters :p_from_time - the starting point of time to *
606 * calculate the virtual meter. *
607 * p_to_time - The end time *
608 * p_cal_avb_time - true for shift available; false otw *
609 * p_shifts - nested table contains the shifts *
610 * p_shifts_idx - current index to the shifts table *
611 * Return Value :Time in hour for the for the shift availability or *
612 * not availability time within the hour specified *
613 **************************************************************************** */
614
615 FUNCTION getSFTInOneHour(p_from_time IN DATE,
616 p_to_time IN DATE,
617 p_cal_avb_time IN BOOLEAN,
618 p_shifts IN shift_array_type,
619 p_shifts_idx IN OUT NOCOPY NUMBER)
620 RETURN NUMBER
621 IS
622 v_hours NUMBER := 0;
623 v_continue BOOLEAN := TRUE;
624 v_from_time DATE := p_from_time;
625 BEGIN
626 IF p_cal_avb_time IS NULL THEN
627 -- Wrong type
628 RETURN 0;
629 END IF;
630
631 WHILE (p_shifts_idx <= p_shifts.Count AND v_from_time < p_to_time AND
632 v_continue) LOOP
633 --Dbms_Output.put_line(' p_shifts_idx : ' || p_shifts_idx);
634 --Dbms_Output.put_line(' p_shifts.Count : ' || p_shifts.Count );
638 -- from time, to time: |----------------|
635 IF (p_to_time < p_shifts(p_shifts_idx).from_date) THEN
636 -- No more shift for the tiem range since the current shift
637 -- is beyond the time range
639 -- shift start and end: |---------------------|
640 v_continue := FALSE;
641 ELSIF (v_from_time > p_shifts(p_shifts_idx).to_date) THEN
642 -- The current time range is ahead of the shift, move the shift forward
643 -- from time, to time: |----------------|
644 -- shift start and end: |---------------------|
645 p_shifts_idx := p_shifts_idx + 1;
646 -- there is intersection between the shift and the time range
647 ELSIF (p_to_time >= p_shifts(p_shifts_idx).from_date AND
648 p_to_time <= p_shifts(p_shifts_idx).To_Date) THEN
649 -- to_time is within the range of the shift
650 -- from time, to time: |----------------|
651 -- shift start and end: |----------------------------|
652 -- or
653 -- from time, to time: |----------------|
654 -- shift start and end: |----------------------------|
655 -- time range overlaps with shift, but does not include the end of shift
656 IF (p_cal_avb_time AND p_shifts(p_shifts_idx).availability_flag = 'Y' OR
657 NOT p_cal_avb_time AND p_shifts(p_shifts_idx).availability_flag = 'N')
658 THEN
659 v_hours := v_hours + (Least(p_shifts(p_shifts_idx).To_Date, p_to_time)
660 - Greatest(p_shifts(p_shifts_idx).from_date, v_from_time))
661 * 24 + 1/3600;
662 END IF;
663 v_from_time := Least(p_shifts(p_shifts_idx).To_Date, p_to_time) +
664 1/86400;
665 v_continue := FALSE;
666 ELSIF (p_from_time <= p_shifts(p_shifts_idx).from_date AND
667 p_to_time >= p_shifts(p_shifts_idx).To_Date) THEN
668 -- The shift is within the time range
669 -- from time, to time: |--------------------------------|
670 -- shift start and end: |---------------------|
671 IF (p_cal_avb_time AND p_shifts(p_shifts_idx).availability_flag = 'Y' OR
672 NOT p_cal_avb_time AND p_shifts(p_shifts_idx).availability_flag = 'N')
673 THEN
674 v_hours := v_hours + (Least(p_shifts(p_shifts_idx).To_Date, p_to_time)
675 - Greatest(p_shifts(p_shifts_idx).from_date, v_from_time))
676 * 24 + 1/3600;
677 END IF;
678 v_from_time := Least(p_shifts(p_shifts_idx).To_Date, p_to_time) +
679 1/86400;
680 v_continue := TRUE;
681 p_shifts_idx := p_shifts_idx + 1;
682 ELSIF (p_from_time >= p_shifts(p_shifts_idx).from_date AND
683 p_from_time <= p_shifts(p_shifts_idx).To_Date) THEN
684 -- from time is within the range of the shift, but not the to time.
685 -- from time, to time: |----------------|
686 -- shift start and end: |---------------------|
687 -- time range overlaps with shift, but does not include the end of shift
688 IF (p_cal_avb_time AND p_shifts(p_shifts_idx).availability_flag = 'Y' OR
689 NOT p_cal_avb_time AND p_shifts(p_shifts_idx).availability_flag = 'N')
690 THEN
691 v_hours := v_hours + (Least(p_shifts(p_shifts_idx).To_Date, p_to_time)
692 - Greatest(p_shifts(p_shifts_idx).from_date, v_from_time))
693 * 24 + 1/3600;
694 END IF;
695 v_from_time := Least(p_shifts(p_shifts_idx).To_Date, p_to_time) +
696 1/86400;
697 v_continue := TRUE;
698 p_shifts_idx := p_shifts_idx + 1;
699 END IF;
700 END LOOP;
701 RETURN v_hours;
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 RETURN 0;
706 END getSFTInOneHour;
707
708 /* ****************************************************************************
709 * Function :getCompValuesForSFT *
710 * Description :Get shift availability time in hours for an entity in each *
711 * hour during the given time range. *
712 * File Name :MTHSUSAB.PLS *
713 * Visibility :Private *
714 * Parameters :p_entity_fk_key - type of the entity *
715 * p_entity_type - the ending point of time to *
716 * p_component_value - Component value for shift avail: *
717 * 1) Available AVB *
718 * 2) Non-Available NAVB *
719 * p_from_time - the starting point of time to *
720 * calculate the virtual meter. *
721 * p_to_time - the ending point of time to *
722 * calculate the virtual meter *
723 * Return Value :Collection of shift availability time for each *
724 * hour during the given time range. *
725 **************************************************************************** */
726
727 FUNCTION getCompValuesForSFT(p_entity_fk_key IN NUMBER,
728 p_entity_type IN VARCHAR2,
729 p_component_value IN VARCHAR2,
730 p_from_time IN DATE,
731 p_to_time IN DATE) RETURN DBMS_SQL.NUMBER_TABLE
732 IS
733 CURSOR c_getShifts(p_entity_type IN VARCHAR2, p_entity_fk_key IN VARCHAR2,
734 p_from_time IN DATE, p_to_time IN DATE) IS
735 SELECT from_date, To_Date, availability_flag
736 FROM MTH_EQUIPMENT_SHIFTS_D
737 WHERE equipment_fk_key = p_entity_fk_key AND
738 UPPER(entity_type) = p_entity_type AND
742
739 To_Date >= p_from_time AND
740 from_date <= p_to_time
741 ORDER BY from_date;
743 v_shifts shift_array_type;
744 v_comp_values DBMS_SQL.NUMBER_TABLE;
745
746 v_num_elements NUMBER;
747 v_from_time DATE := p_from_time;
748 v_to_time DATE;
749 v_end_from_time DATE := p_to_time - 1/24;
750 v_one_hour NUMBER := 1/24;
751 i NUMBER := 1;
752 v_value NUMBER;
753 -- Record the current index for the shifts nested table v_shifts
754 v_shifts_idx NUMBER := 1;
755 v_for_shift_available BOOLEAN;
756
757
758 BEGIN
759 OPEN c_getShifts(p_entity_type, p_entity_fk_key,p_from_time, p_to_time);
760 FETCH c_getShifts BULK COLLECT INTO v_shifts;
761 CLOSE c_getShifts;
762
763 v_num_elements := Trunc((p_to_time - p_from_time) * 24) + 1;
764 IF (p_component_value = 'AVB') THEN
765 v_for_shift_available := TRUE;
766 ELSIF (p_component_value = 'NAVB') THEN
767 v_for_shift_available := FALSE;
768 ELSE
769 v_for_shift_available := NULL;
770 END IF;
771 WHILE (v_from_time <= v_end_from_time) LOOP
772 v_to_time := v_from_time + 1/24 - 1/86400;
773 v_comp_values(i) := getSFTInOneHour(v_from_time,
774 v_to_time,
775 v_for_shift_available,
776 v_shifts,
777 v_shifts_idx);
778 i := i + 1;
779 v_from_time := v_from_time + v_one_hour;
780 END LOOP;
781
782 RETURN v_comp_values;
783 END getCompValuesForSFT;
784
785
786 /* ****************************************************************************
787 * Function :getstatusTimeInOneHour *
788 * Description :Get shift availability time in the specified hour for the *
789 * equipment power rating. If it is not an equipment, return 0*
790 * File Name :MTHSUSAB.PLS *
791 * Visibility :Private *
792 * Parameters :p_from_time - the starting point of time to *
793 * calculate the virtual meter. *
794 * p_to_time - The end time *
795 * p_component_value - The possible values are *
796 * 1) Run Time RT *
797 * 2) Down Time DT *
798 * 3) Idle Time IT *
799 * 4) Off Time OT *
800 * p_status - nested table contains the status times *
801 * p_idx - current index to the status table *
802 * Return Value :Time in hour for the for the specified status *
803 * within the hour specified; *
804 NULL if the status is not available for the *
805 * specified time range. Need to wait for the status *
806 * to be available. *
807 **************************************************************************** */
808
809 FUNCTION getstatusTimeInOneHour(p_from_time IN DATE,
810 p_to_time IN DATE,
811 p_component_value IN varchar2,
812 p_status IN status_array_type,
813 p_idx IN OUT NOCOPY NUMBER)
814 RETURN NUMBER
815 IS
816 v_hours NUMBER := 0;
817 v_time_range NUMBER := 0;
818 v_continue BOOLEAN := TRUE;
819 v_from_time DATE := p_from_time;
820 v_time_for_status NUMBER;
821 BEGIN
822 WHILE (p_idx <= p_status.Count AND v_from_time < p_to_time AND
823 v_continue) LOOP
824
825 IF (p_to_time >= p_status(p_idx).from_date AND
826 p_status(p_idx).To_Date IS NULL) THEN
827 -- The status range is still open without ending time. Need to wait until
828 -- the status range is closed in order to calculate the hours for the
829 -- status
830 -- from time, to time: |----------------|
831 -- status start and end: |-------------------...(end time=NULL)
832 v_hours := NULL;
833 v_continue := FALSE;
834 ELSIF (p_to_time < p_status(p_idx).from_date) THEN
835 -- No more status for the time range since the current status range
836 -- is beyond the time range
837 -- from time, to time: |----------------|
838 -- status start and end: |---------------------|
839
840 v_continue := FALSE;
841 ELSIF (v_from_time > p_status(p_idx).to_date) THEN
842 -- The current time range is ahead of the status range, move the status
843 -- range forward
844 -- from time, to time: |----------------|
845 -- status start and end: |---------------------|
846
847 p_idx := p_idx + 1;
848 -- there is intersection between the status range and the time range
849 ELSIF (p_to_time >= p_status(p_idx).from_date AND
850 p_to_time <= p_status(p_idx).To_Date) THEN
851 -- to_time is within the range of the status range
852 -- from time, to time: |----------------|
853 -- status start and end: |----------------------------|
854 -- or
855 -- from time, to time: |----------------|
856 -- status start and end: |----------------------------|
857 -- time range overlaps with status, but does not include end of status
861
858 v_time_range := (Least(p_status(p_idx).To_Date, p_to_time) -
859 Greatest(p_status(p_idx).from_date, v_from_time)) + 1/86400;
860 --Dbms_Output.put_line(' case 2 v_hours before : ' || v_hours);
862 v_hours := v_hours + v_time_range / (p_status(p_idx).to_date -
863 p_status(p_idx).from_date +
864 1/86400) *
865 CASE p_component_value
866 WHEN 'RT' THEN Nvl(p_status(p_idx).run_hours, 0)
867 WHEN 'DT' THEN Nvl(p_status(p_idx).down_hours, 0)
868 WHEN 'IT' THEN Nvl(p_status(p_idx).idle_hours, 0)
869 WHEN 'OT' THEN Nvl(p_status(p_idx).off_hours, 0)
870 ELSE 0
871 END;
872 --v_from_time := Least(p_status(p_idx).To_Date, p_to_time) + 1/86400;
873 v_continue := FALSE;
874 --Dbms_Output.put_line(' case 2 v_hours : ' || v_hours);
875
876 ELSIF (p_from_time <= p_status(p_idx).from_date AND
877 p_to_time >= p_status(p_idx).To_Date) THEN
878 -- The status record is within the time range
879 -- from time, to time: |--------------------------------|
880 -- status start and end: |------------------|
881 --v_time_range := (Least(p_status(p_idx).To_Date, p_to_time) -
882 -- Greatest(p_status(p_idx).from_date, v_from_time)) + 1/86400;
883
884 -- v_hours := v_hours + v_time_range / (p_status(p_idx).to_date -
885 -- p_status(p_idx).from_date +
886 -- 1/86400) *
887 v_hours := v_hours + CASE p_component_value
888 WHEN 'RT' THEN Nvl(p_status(p_idx).run_hours, 0)
889 WHEN 'DT' THEN Nvl(p_status(p_idx).down_hours, 0)
890 WHEN 'IT' THEN Nvl(p_status(p_idx).idle_hours, 0)
891 WHEN 'OT' THEN Nvl(p_status(p_idx).off_hours, 0)
892 ELSE 0
893 END;
894 -- v_from_time := Least(p_status(p_idx).To_Date, p_to_time) + 1/86400;
895 v_continue := TRUE;
896 p_idx := p_idx + 1;
897 --Dbms_Output.put_line(' case 3 v_hours : ' || v_hours);
898
899 ELSIF (p_from_time >= p_status(p_idx).from_date AND
900 p_from_time <= p_status(p_idx).To_Date) THEN
901 -- from time is within the range of the status range, but not the to time.
902 -- from time, to time: |----------------|
903 -- status start and end: |---------------------|
904 -- time range overlaps with status, but does not include the end of status
905 v_time_range := (Least(p_status(p_idx).To_Date, p_to_time) -
906 Greatest(p_status(p_idx).from_date, v_from_time)) + 1/86400;
907 v_hours := v_hours + v_time_range / (p_status(p_idx).to_date -
908 p_status(p_idx).from_date +
909 1/86400) *
910 CASE p_component_value
911 WHEN 'RT' THEN Nvl(p_status(p_idx).run_hours, 0)
912 WHEN 'DT' THEN Nvl(p_status(p_idx).down_hours, 0)
913 WHEN 'IT' THEN Nvl(p_status(p_idx).idle_hours, 0)
914 WHEN 'OT' THEN Nvl(p_status(p_idx).off_hours, 0)
915 ELSE 0
916 END ;
917 --Dbms_Output.put_line(' case 4 v_hours : ' || v_hours);
918
919 -- v_from_time := Least(p_status(p_idx).To_Date, p_to_time) + 1/86400;
920 v_continue := TRUE;
921 p_idx := p_idx + 1;
922
923 END IF;
924 END LOOP;
925 RETURN v_hours;
926
927 EXCEPTION
928 WHEN OTHERS THEN
929 RETURN 0;
930 END getstatusTimeInOneHour;
931
932
933 /* ****************************************************************************
934 * Function :getCompValuesForEquipStatus *
935 * Description :Get run/idle/down/off time for the specified hour. If it *
936 * is not an equipment, return 0. *
937 * File Name :MTHSUSAB.PLS *
938 * Visibility :Private *
939 * Parameters :p_entity_fk_key - type of the entity *
940 * p_entity_type - the ending point of time to *
941 * p_component_value - Component value for shift avail: *
942 * 1) Run Time RT *
943 * 2) Down Time DT *
944 * 3) Idle Time IT *
945 * 4) Off Time OT *
946 * p_from_time - the starting point of time to *
947 * calculate the virtual meter. *
948 * p_to_time - the ending point of time to *
949 * calculate the virtual meter *
950 * Return Value :Power rating of the equipment. If it is not an *
951 equipment, return 0. *
952 **************************************************************************** */
953
954 FUNCTION getCompValuesForEquipStatus(p_entity_fk_key IN NUMBER,
955 p_entity_type IN VARCHAR2,
956 p_component_value IN VARCHAR2,
957 p_from_time IN DATE,
958 p_to_time IN DATE)
959 RETURN DBMS_SQL.NUMBER_TABLE
960 IS
964 SELECT actual_from_date, actual_to_date, run_hours, down_hours,
961 CURSOR c_getEquipStatusTime(p_equip_fk_key IN VARCHAR2,
962 p_from_time IN DATE,
963 p_to_time IN DATE) IS
965 idle_hours, off_hours
966 FROM MTH_EQUIP_PROD_PERFORMANCE_F
967 WHERE equipment_fk_key = p_equip_fk_key AND
968 actual_to_date >= p_from_time AND
969 actual_from_date <= p_to_time AND
970 actual_to_date IS NOT NULL
971 ORDER BY actual_from_date;
972
973 v_status status_array_type;
974 v_comp_values DBMS_SQL.NUMBER_TABLE;
975
976 --v_num_elements NUMBER;
977 v_from_time DATE := p_from_time;
978 v_to_time DATE;
979 v_end_from_time DATE := p_to_time - 1/24;
980 v_one_hour NUMBER := 1/24;
981 i NUMBER := 1;
982 --v_value NUMBER;
983 -- Record the current index for the shifts nested table v_shifts
984 v_idx NUMBER := 1;
985 v_continue BOOLEAN := TRUE;
986 v_comp_value NUMBER;
987 v_data_end_time DATE;
988
989 BEGIN
990
991 IF (p_entity_type <> 'EQUIPMENT') THEN
992 RETURN v_comp_values;
993 END IF;
994
995 OPEN c_getEquipStatusTime(p_entity_fk_key,
996 p_from_time, p_to_time);
997 FETCH c_getEquipStatusTime BULK COLLECT INTO v_status;
998 CLOSE c_getEquipStatusTime;
999
1000 IF (v_status IS NOT NULL AND v_status.Count > 0) THEN
1001 v_data_end_time := v_status(v_status.Count).to_date;
1002 ELSE
1003 v_data_end_time := v_from_time - 1;
1004 END IF;
1005
1006 v_end_from_time := Least(v_end_from_time,
1007 trunc(v_data_end_time - 1/24 + 1/86400, 'HH24'));
1008
1009 WHILE (v_from_time <= v_end_from_time AND
1010 v_status IS NOT NULL AND
1011 v_idx <= v_status.Count AND
1012 v_continue) LOOP
1013 v_to_time := v_from_time + 1/24 - 1/86400;
1014 v_comp_value := getstatusTimeInOneHour(v_from_time,
1015 v_to_time,
1016 p_component_value,
1017 v_status,
1018 v_idx);
1019 IF ( v_comp_value IS NOT NULL) THEN
1020 v_comp_values(i) := v_comp_value;
1021 i := i + 1;
1022 v_from_time := v_from_time + v_one_hour;
1023 ELSE
1024 -- The status is not available for this hour
1025 v_continue := FALSE;
1026 END IF;
1027 END LOOP;
1028 RETURN v_comp_values;
1029
1030 END getCompValuesForEquipStatus;
1031
1032
1033 /* ****************************************************************************
1034 * Function :getMeterReadingInOneHour *
1035 * Description :Get shift availability time in the specified hour for the *
1036 * equipment power rating. If it is not an equipment, return 0*
1037 * File Name :MTHSUSAB.PLS *
1038 * Visibility :Private *
1039 * Parameters :p_from_time - the starting point of time to *
1040 * calculate the virtual meter. *
1041 * p_to_time - The end time *
1042 * p_component_value - The possible values are *
1043 * 1) Run Time RT *
1044 * 2) Down Time DT *
1045 * 3) Idle Time IT *
1046 * 4) Off Time OT *
1047 * p_status - nested table contains the status times *
1048 * p_idx - current index to the status table *
1049 * Return Value :Time in hour for the for the specified status *
1050 * within the hour specified; *
1051 NULL if the status is not available for the *
1052 * specified time range. Need to wait for the status *
1053 * to be available. *
1054 **************************************************************************** */
1055
1056 FUNCTION getMeterReadingInOneHour(p_from_time IN DATE,
1057 p_to_time IN DATE,
1058 p_meter_fk_key IN VARCHAR2,
1059 p_meter_readings IN readings_array_type,
1060 p_idx IN OUT NOCOPY NUMBER)
1061 RETURN NUMBER
1062 IS
1063 v_usage_value NUMBER := 0;
1064 v_time_range NUMBER := 0;
1065 v_continue BOOLEAN := TRUE;
1066 v_from_time DATE := p_from_time;
1067 v_meter_value NUMBER;
1068 BEGIN
1069 WHILE (p_idx <= p_meter_readings.Count AND v_from_time < p_to_time AND
1070 v_continue) LOOP
1071
1072 IF (p_to_time > p_meter_readings(p_idx).from_date AND
1073 p_meter_readings(p_idx).To_Date IS NULL) THEN
1074 -- The meter range is still open without ending time. Need to wait until
1075 -- the status range is closed in order to calculate the hours for the
1076 -- status
1077 -- from time, to time: |----------------|
1078 -- status start and end: |-------------------...(end time=NULL)
1079 v_usage_value := NULL;
1080 v_continue := FALSE;
1081 ELSIF (p_to_time < p_meter_readings(p_idx).from_date) THEN
1082 -- No more status for the time range since the current status range
1083 -- is beyond the time range
1084 -- from time, to time: |----------------|
1088 ELSIF (v_from_time > p_meter_readings(p_idx).to_date) THEN
1085 -- status start and end: |---------------------|
1086
1087 v_continue := FALSE;
1089 -- The current time range is ahead of the status range, move the status
1090 -- range forward
1091 -- from time, to time: |----------------|
1092 -- status start and end: |---------------------|
1093
1094 p_idx := p_idx + 1;
1095 -- there is intersection between the status range and the time range
1096 ELSIF (p_to_time >= p_meter_readings(p_idx).from_date AND
1097 p_to_time <= p_meter_readings(p_idx).To_Date) THEN
1098 -- to_time is within the range of the status range
1099 -- from time, to time: |----------------|
1100 -- status start and end: |----------------------------|
1101 -- or
1102 -- from time, to time: |----------------|
1103 -- status start and end: |----------------------------|
1104 -- time range overlaps with status, but does not include end of status
1105
1106 v_time_range := (Least(p_meter_readings(p_idx).To_Date, p_to_time) -
1107 Greatest(p_meter_readings(p_idx).from_date, v_from_time))
1108 + 1/86400;
1109 v_usage_value := v_usage_value +
1110 v_time_range / (p_meter_readings(p_idx).to_date -
1111 p_meter_readings(p_idx).from_date +
1112 1/86400) *
1113 p_meter_readings(p_idx).usage_value;
1114 v_from_time := Least(p_meter_readings(p_idx).To_Date, p_to_time) +
1115 1/86400;
1116 v_continue := FALSE;
1117
1118 ELSIF (p_from_time <= p_meter_readings(p_idx).from_date AND
1119 p_to_time >= p_meter_readings(p_idx).To_Date) THEN
1120 -- The status record is within the time range
1121 -- from time, to time: |--------------------------------|
1122 -- shift start and end: |------------------|
1123 --v_time_range := (Least(p_status(p_idx).To_Date, p_to_time) -
1124 -- Greatest(p_status(p_idx).from_date, v_from_time)) + 1/86400;
1125
1126 -- v_hours := v_hours + v_time_range / (p_status(p_idx).to_date -
1127 -- p_status(p_idx).from_date +
1128 -- 1/86400) *
1129 v_usage_value := v_usage_value + p_meter_readings(p_idx).usage_value;
1130 v_from_time := Least(p_meter_readings(p_idx).To_Date, p_to_time) +
1131 1/86400;
1132 v_continue := TRUE;
1133 p_idx := p_idx + 1;
1134
1135 ELSIF (p_from_time >= p_meter_readings(p_idx).from_date AND
1136 p_from_time <= p_meter_readings(p_idx).To_Date) THEN
1137 -- from time is within the range of the status range, but not the to time.
1138 -- from time, to time: |----------------|
1139 -- status start and end: |---------------------|
1140 -- time range overlaps with status, but does not include the end of status
1141 v_time_range := (Least(p_meter_readings(p_idx).To_Date, p_to_time) -
1142 Greatest(p_meter_readings(p_idx).from_date, v_from_time)) +
1143 1/86400;
1144 v_usage_value := v_usage_value +
1145 v_time_range / (p_meter_readings(p_idx).to_date -
1146 p_meter_readings(p_idx).from_date +
1147 1/86400) *
1148 p_meter_readings(p_idx).usage_value;
1149
1150 v_from_time := Least(p_meter_readings(p_idx).To_Date, p_to_time) +
1151 1/86400;
1152
1153 v_continue := TRUE;
1154 --ELSE
1155 --Dbms_Output.put_line(' case 4 p_idx : ' || p_idx);
1156
1157 END IF;
1158 END LOOP;
1159 RETURN v_usage_value;
1160 END getMeterReadingInOneHour;
1161
1162
1163 /* ****************************************************************************
1164 * Function :getCompValuesForMeter *
1165 * Description :Get run/idle/down/off time for the specified hour. If it *
1166 * is not an equipment, return 0. *
1167 * File Name :MTHSUSAB.PLS *
1168 * Visibility :Private *
1169 * Parameters :p_meter_fk_key - meter fk key *
1170 * p_entity_type - the ending point of time to *
1171 * p_component_value - meter fk key *
1172 * p_from_time - the starting point of time to *
1173 * calculate the virtual meter. *
1174 * p_to_time - the ending point of time to *
1175 * calculate the virtual meter *
1176 * Return Value :Array of meter readings with type readings_array_type*
1177 **************************************************************************** */
1178
1179 FUNCTION getCompValuesForMeter(p_meter_fk_key IN NUMBER,
1180 p_from_time IN DATE,
1181 p_to_time IN DATE)
1182 RETURN DBMS_SQL.NUMBER_TABLE
1183 IS
1184 CURSOR c_getMeterReadings(p_meter_fk_key IN NUMBER,
1185 p_from_time IN DATE,
1186 p_to_time IN DATE) IS
1187
1188 SELECT from_time, to_time, usage_value
1189 FROM mth_meter_readings
1190 WHERE meter_fk_key = p_meter_fk_key AND
1191 to_time >= p_from_time AND
1192 from_time <= p_to_time
1193 ORDER BY from_time;
1194
1195 v_meter_readings readings_array_type;
1199 v_from_time DATE := p_from_time;
1196 v_comp_values DBMS_SQL.NUMBER_TABLE;
1197
1198 v_num_elements NUMBER;
1200 v_to_time DATE;
1201 v_end_from_time DATE := p_to_time - 1/24;
1202 v_one_hour NUMBER := 1/24;
1203 i NUMBER := 1;
1204 v_value NUMBER;
1205 -- Record the current index for the shifts nested table v_shifts
1206 v_idx NUMBER := 1;
1207 v_continue BOOLEAN := TRUE;
1208 v_comp_value NUMBER;
1209 v_data_end_time DATE;
1210
1211
1212 BEGIN
1213 OPEN c_getMeterReadings(p_meter_fk_key, p_from_time, p_to_time);
1214 FETCH c_getMeterReadings BULK COLLECT INTO v_meter_readings;
1215 CLOSE c_getMeterReadings;
1216
1217 IF (v_meter_readings IS NOT NULL AND v_meter_readings.Count > 0) THEN
1218 v_data_end_time := v_meter_readings(v_meter_readings.Count).To_Date;
1219 ELSE
1220 v_data_end_time := v_from_time - 1;
1221 END IF;
1222 --Dbms_Output.put_line( ' v_data_end_time ' || v_data_end_time);
1223
1224 --Dbms_Output.put_line( ' p_to_time ' || p_to_time);
1225
1226 v_end_from_time := Least(v_end_from_time,
1227 trunc(v_data_end_time - 1/24 + 1/86400, 'HH24'));
1228 --Dbms_Output.put_line( ' v_end_from_time ' || v_end_from_time);
1229
1230 v_num_elements := Trunc((p_to_time - p_from_time) * 24) + 1;
1231 WHILE (v_from_time <= v_end_from_time AND
1232 v_meter_readings IS NOT NULL AND
1233 v_idx <= v_meter_readings.Count AND
1234 v_continue) LOOP
1235 v_to_time := v_from_time + 1/24 - 1/86400;
1236 v_comp_value := getMeterReadingInOneHour(v_from_time,
1237 v_to_time,
1238 p_meter_fk_key,
1239 v_meter_readings,
1240 v_idx);
1241
1242
1243 -- TO DO: Need to check whether that is the last reading and the
1244 -- end time of the last record is less than the from time
1245 --- If so, need to stop there.
1246 IF ( v_comp_value IS NOT NULL) THEN
1247 v_comp_values(i) := v_comp_value;
1248 i := i + 1;
1249 v_from_time := v_from_time + v_one_hour;
1250 ELSE
1251 -- The status is not available for this hour
1252 v_continue := FALSE;
1253 END IF;
1254 END LOOP;
1255
1256 RETURN v_comp_values;
1257 END getCompValuesForMeter;
1258
1259
1260 /* ****************************************************************************
1261 * Function :getCompValuesForCustom *
1262 * Description :Call custom API to get the usage value for custom type *
1263 * File Name :MTHSUSAB.PLS *
1264 * Visibility :Private *
1265 * Parameters :p_entity_fk_key - type of the entity *
1266 * p_entity_type - the ending point of time to *
1267 * p_component_value - Component value *
1268 * p_from_time - the starting point of time to *
1269 * calculate the virtual meter. *
1270 * p_to_time - the ending point of time to *
1271 * calculate the virtual meter *
1272 * Return Value :Usage value for each hour for the time duration *
1273 * specified *
1274 **************************************************************************** */
1275
1276 FUNCTION getCompValuesForCustom(p_entity_fk_key IN NUMBER,
1277 p_entity_type IN VARCHAR2,
1278 p_component_value IN VARCHAR2,
1279 p_from_time IN DATE,
1280 p_to_time IN DATE)
1281 RETURN DBMS_SQL.NUMBER_TABLE
1282 IS
1283 CURSOR c_get_custom_api IS
1284
1285 SELECT DESCRIPTION
1286 FROM FND_LOOKUPS
1287 WHERE LOOKUP_CODE = 'CUSTOM_SUSTAIN_ASPECT_API' AND
1288 LOOKUP_TYPE ='MTH_CUSTOM_PLSQL_API';
1289
1290 v_api_name VARCHAR2(200) := NULL;
1291 v_stmt VARCHAR2(4000);
1292 v_comp_values DBMS_SQL.NUMBER_TABLE;
1293
1294 v_num_elements NUMBER;
1295 v_from_time DATE := p_from_time;
1296 v_to_time DATE;
1297 v_end_from_time DATE := p_to_time - 1/24;
1298 v_one_hour NUMBER := 1/24;
1299 i NUMBER := 1;
1300 v_value NUMBER;
1301 -- Record the current index for the shifts nested table v_shifts
1302 v_idx NUMBER := 1;
1303 v_continue BOOLEAN := TRUE;
1304 v_comp_value NUMBER;
1305 v_err_msg VARCHAR2(4000);
1306 v_err_code NUMBER;
1307
1308
1309 BEGIN
1310 SELECT DESCRIPTION INTO v_api_name
1311 FROM FND_LOOKUPS
1312 WHERE LOOKUP_CODE = 'CUSTOM_SUSTAIN_ASPECT_API' AND
1313 LOOKUP_TYPE ='MTH_CUSTOM_PLSQL_API';
1314
1315 IF (sql%NOTFOUND) THEN
1316
1317 v_err_msg :=
1318 'Could not find the custom API to calculate sustainability aspect usage';
1319
1320 INSERT INTO mth_runtime_err
1321 ( MODULE, error_msg, timestamp) VALUES
1322 ('MTH_SUSTAIN_ASPECT_PKG.getCompValuesForCustom', v_err_msg, SYSDATE);
1323 ELSE
1324 -- API signature:
1325 -- v_api_name(p_entity_fk_key IN NUMBER,
1326 -- p_entity_type IN VARCHAR2,
1327 -- p_component_value IN VARCHAR2,
1328 -- p_from_time IN DATE,
1329 -- p_to_time IN DATE)
1330 v_stmt := 'SELECT ' || v_api_name || '(:1, ' || -- p_entity_type
1331 ':2, ' || -- p_entity_type
1335 ' FROM DUAL';
1332 ':3, ' || -- p_component_value
1333 ':4, ' || -- p_from_time
1334 ':5)' || -- p_to_time
1336 --Dbms_Output.put_line(' v_stmt ' || v_stmt);
1337 v_num_elements := Trunc((p_to_time - p_from_time) * 24) + 1;
1338 WHILE (v_from_time <= v_end_from_time AND v_continue) LOOP
1339 v_to_time := v_from_time + 1/24 - 1/86400;
1340 EXECUTE IMMEDIATE v_stmt INTO v_comp_value USING p_entity_fk_key,
1341 p_entity_type,
1342 p_component_value,
1343 v_from_time,
1344 v_to_time;
1345
1346
1347
1348 -- TO DO: Need to check whether that is the last reading and the
1349 -- end time of the last record is less than the from time
1350 --- If so, need to stop there.
1351 IF ( v_comp_value IS NOT NULL) THEN
1352 v_comp_values(i) := v_comp_value;
1353 i := i + 1;
1354 v_from_time := v_from_time + v_one_hour;
1355 ELSE
1356 -- The status is not available for this hour
1357 v_continue := FALSE;
1358 END IF;
1359 END LOOP;
1360
1361 END IF;
1362
1363 RETURN v_comp_values;
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 v_err_msg := SQLERRM;
1368 v_err_code := SQLCODE;
1369
1370 INSERT INTO mth_runtime_err
1371 ( MODULE, error_code, error_msg, timestamp) VALUES
1372 ('MTH_SUSTAIN_ASPECT_PKG.getCompValuesForCustom',
1373 v_err_code, v_err_msg, SYSDATE);
1374
1375 RETURN v_comp_values;
1376
1377 END getCompValuesForCustom;
1378
1379
1380 /* ****************************************************************************
1381 * Function :calculate_virtual_component *
1382 * Description :Calculate the virtual component for the specified time *
1383 * in every hour *
1384 * File Name :MTHSUSAB.PLS *
1385 * Visibility :Private *
1386 * Parameters :p_component_type - component type could be: *
1387 * MTH_METER_BASED_COMPONENT Constant CONST *
1388 * MTH_METER_BASED_COMPONENT Meter METER *
1389 * MTH_PWR_COMPONENT Entity Power Rating EPR *
1390 * MTH_PWR_COMPONENT Equipment Status ES *
1391 * MTH_PWR_COMPONENT Shift Availibility SFT *
1392 * MTH_CUST_COMPONENT Custom Component CC *
1393 * p_component_value - Component value depends on type *
1394 * 1) Meter Based: MB *
1395 * 2) Power rating Based: RPB *
1396 * 3) Custom : CB *
1397 * p_entity_type - Associated entity type for calcuation*
1398 * p_entity_fk_key - Assocuated entity fk key *
1399 * p_from_time - the starting point of time to *
1400 * calculate the virtual meter. *
1401 * p_to_time - the ending point of time to *
1402 * calculate the virtual meter *
1403 * Return Value :List of values of the components for each hour *
1404 * within the from time and to time. The type of the *
1405 * return value is DBMS_SQL.NUMBER_TABLE *
1406 **************************************************************************** */
1407
1408 FUNCTION calculate_virtual_component(p_component_type IN VARCHAR2,
1409 p_component_value IN VARCHAR2,
1410 p_entity_type IN VARCHAR2,
1411 p_entity_fk_key IN VARCHAR2,
1412 p_from_time IN DATE,
1413 p_to_time IN DATE)
1414 RETURN DBMS_SQL.NUMBER_TABLE
1415
1416 IS
1417 v_comp_values DBMS_SQL.NUMBER_TABLE;
1418 v_num_elements NUMBER;
1419 v_from_time DATE := p_from_time;
1420 v_end_from_time DATE := p_to_time - 1/24;
1421 v_one_hour NUMBER := 1/24;
1422 i NUMBER := 1;
1423 v_value NUMBER;
1424 BEGIN
1425 v_num_elements := Trunc((p_to_time - p_from_time) * 24) + 1;
1426 -- v_comp_values := DBMS_SQL.NUMBER_TABLE(v_num_elements);
1427 IF (p_component_type = 'CONST') THEN
1428 -- 1. Support CONST type component
1429 v_value := To_Number(p_component_value);
1430 WHILE (v_from_time <= v_end_from_time) LOOP
1431 v_comp_values(i) := v_value;
1432 i := i + 1;
1433 v_from_time := v_from_time + v_one_hour;
1434 END LOOP;
1435 ELSIF (p_component_type = 'EPR') THEN
1436 -- 2. Support Entity Power Rating type component
1437 v_value := getEquipPowerRating(p_entity_fk_key, p_entity_type);
1438 WHILE (v_from_time <= v_end_from_time) LOOP
1439 v_comp_values(i) := v_value;
1440 i := i + 1;
1441 v_from_time := v_from_time + v_one_hour;
1442 END LOOP;
1443 ELSIF (p_component_type = 'SFT') THEN
1444 -- 3. Support Shift Availibility type componen
1445 v_comp_values := getCompValuesForSFT(p_entity_fk_key,
1446 p_entity_type,
1447 p_component_value,
1448 p_from_time,
1452 v_comp_values := getCompValuesForEquipStatus(p_entity_fk_key,
1449 p_to_time);
1450 ELSIF (p_component_type = 'ES') THEN
1451 -- 4. Support Equipment Status type componen
1453 p_entity_type,
1454 p_component_value,
1455 p_from_time,
1456 p_to_time);
1457
1458 ELSIF (p_component_type = 'METER') THEN
1459 -- 5. Support Meter type componen
1460 -- getCompValuesForMeter
1461
1462 v_comp_values := getCompValuesForMeter(To_Number(p_component_value),
1463 p_from_time,
1464 p_to_time);
1465 ELSIF (p_component_type = 'CC') THEN
1466 -- 6. Support Custom Component type componen
1467 v_comp_values := getCompValuesForCustom(p_entity_fk_key,
1468 p_entity_type,
1469 p_component_value,
1470 p_from_time,
1471 p_to_time);
1472
1473 END IF;
1474
1475
1476 RETURN v_comp_values;
1477 END calculate_virtual_component;
1478
1479
1480 /* ****************************************************************************
1481 * Procedure :cal_virtual_meter_component *
1482 * Description :Calculate all the virtual meters that is power rating*
1483 * based hourly if the components are available and *
1484 * load the data into meter readings table MTH_METER_READINGS *
1485 * File Name :MTHSUSAB.PLS *
1486 * Visibility :Private *
1487 * Parameters :p_meter_pk_key - meter pk key *
1488 * p_virtual_meter_type - virtual meter type. It can be *
1489 * 1) Meter Based: MB *
1490 * 2) Powering rating Based: RPB*
1491 * 3) Custom : CB *
1492 * p_virtual_meter_formula - Formula to calculate meter *
1493 * p_entity_type - Associated entity type for calcuation*
1494 * p_entity_fk_key - Assocuated entity fk key *
1495 * p_from_time - the starting point of time to *
1496 * calculate the virtual meter. *
1497 * p_to_time - the ending point of time to *
1498 * calculate the virtual meter *
1499 * Return Value :None *
1500 **************************************************************************** */
1501
1502 FUNCTION cal_virtual_meter_component(p_meter_pk_key IN NUMBER,
1503 p_virtual_meter_type IN VARCHAR2,
1504 p_virtual_meter_formula IN VARCHAR2,
1505 p_entity_type IN VARCHAR2,
1506 p_entity_fk_key IN VARCHAR2,
1507 p_from_time IN DATE,
1508 p_to_time IN DATE)
1509 RETURN component_lookup_type
1510 IS
1511 CURSOR c_getMeterComponents(p_meter_fk_key IN NUMBER) IS
1512 SELECT virtual_meter_component_pk_key as component_pk_key,
1513 component_type, component_value
1514 FROM MTH_VIRTUAL_METER_COMPONENTS
1515 WHERE meter_fk_key = p_meter_fk_key;
1516
1517 v_one_comp component_record;
1518 v_compoents component_lookup_type;
1519 i NUMBER := 1;
1520
1521 BEGIN
1522 -- 1. Get all the virtual component components
1523 FOR r_comp IN c_getMeterComponents(p_meter_pk_key) LOOP
1524 v_one_comp.component_pk_key := r_comp.component_pk_key;
1525 v_one_comp.component_type := r_comp.component_type;
1526 v_one_comp.component_value := r_comp.component_value;
1527 v_one_comp.comp_time_series_values :=
1528 calculate_virtual_component(r_comp.component_type,
1529 r_comp.component_value,
1530 p_entity_type,
1531 p_entity_fk_key,
1532 p_from_time,
1533 p_to_time);
1534
1535 v_compoents(i) := v_one_comp;
1536 i := i + 1;
1537 END LOOP;
1538
1539 RETURN v_compoents;
1540 END cal_virtual_meter_component;
1541
1542
1543
1544 /* ****************************************************************************
1545 * Function :calVirtualMeter *
1546 * Description :Calculate the virtual meter using the formula with all the *
1547 * components available for pne hour *
1548 * File Name :MTHSUSAB.PLS *
1549 * Visibility :Private *
1550 * Parameters :p_formula - Virtual meter formula *
1551 * p_components - All the components with all their *
1552 * values for each component *
1553 * p_idx - The index in p_components that indicates *
1554 * the set of components to be calculated. *
1555 * Return Value :Usage value calcuated using the formula *
1556 **************************************************************************** */
1557
1561 IS
1558 FUNCTION calVirtualMeter(p_cursor IN NUMBER, -- p_formula IN VARCHAR2,
1559 p_components IN component_lookup_type,
1560 p_idx IN NUMBER) RETURN NUMBER
1562 v_usageValue NUMBER := 0;
1563 v_stmt VARCHAR2(4000);
1564 c NUMBER;
1565 dummy NUMBER;
1566 BEGIN
1567
1568
1569 -- 1. Construct the dynamic sql statement to calculate virtual meter formula
1570 DBMS_SQL.DEFINE_COLUMN(p_cursor, 1, v_usageValue);
1571
1572 FOR i IN 1..p_components.Count LOOP
1573 DBMS_SQL.BIND_VARIABLE(p_cursor, ':' || i,
1574 p_components(i).comp_time_series_values(p_idx));
1575 END LOOP;
1576
1577 dummy := DBMS_SQL.EXECUTE(p_cursor);
1578 IF DBMS_SQL.FETCH_ROWS(p_cursor)>0 THEN
1579 -- get column values of the row
1580 -- Dbms_Output.put_line( ' DBMS_SQL.FETCH_ROWS(c)>0 : ' );
1581 DBMS_SQL.COLUMN_VALUE(p_cursor, 1, v_usageValue);
1582 END IF;
1583
1584 --DBMS_SQL.CLOSE_CURSOR(c);
1585 RETURN v_usageValue;
1586 /*
1587 EXCEPTION WHEN OTHERS THEN
1588 IF DBMS_SQL.IS_OPEN(c) THEN
1589 DBMS_SQL.CLOSE_CURSOR(c);
1590 END IF;
1591
1592 RETURN v_usageValue;
1593 */
1594
1595 END calVirtualMeter;
1596
1597
1598 /* ****************************************************************************
1599 * Procedure :cal_save_virtual_meter *
1600 * Description :Calculate virtual meter using the component values and *
1601 * load the data into meter readings table MTH_METER_READINGS *
1602 * File Name :MTHSUSAB.PLS *
1603 * Visibility :Private *
1604 * Parameters :p_meter_pk_key - meter pk key *
1605 * p_formula - Virtual meter formula to calculate meter *
1606 * p_components - All the components with all their *
1607 * values for each component *
1608 * p_virtual_meter_formula - Formula to calculate meter *
1609 * p_entity_type - Associated entity type for calcuation*
1610 * p_entity_fk_key - Assocuated entity fk key *
1611 * p_start_time - the starting point of time to *
1612 * calculate the virtual meter. *
1613 * p_end_time - the ending point of time to *
1614 * calculate the virtual meter *
1615 * p_bulk_commit_size - max commit size *
1616 * p_num_trans - current number of insert/update *
1617 * Return Value :None *
1618 **************************************************************************** */
1619 PROCEDURE cal_save_virtual_meter(p_meter_fk_key IN NUMBER,
1620 p_formula IN VARCHAR2,
1621 p_components IN component_lookup_type,
1622 p_start_time IN DATE,
1623 p_end_time IN DATE)
1624
1625 IS
1626 v_numElements NUMBER := NULL;
1627 v_usage_value NUMBER;
1628 v_hour_start_time DATE := p_start_time;
1629 v_hour_end_time DATE;
1630 v_system_id NUMBER := -99999;
1631 v_stmt VARCHAR2(4000);
1632 c NUMBER;
1633 v_err_msg VARCHAR2(4000);
1634 v_err_code NUMBER;
1635 BEGIN
1636 -- 1. Find out the number of entries to be inserted, which should be the
1637 -- the least one among them
1638 FOR i IN 1..p_components.Count LOOP
1639 IF (v_numElements IS NULL OR
1640 p_components(i).comp_time_series_values.Count < v_numElements) THEN
1641 v_numElements := p_components(i).comp_time_series_values.Count;
1642
1643 END IF;
1644 END LOOP;
1645
1646 IF (v_numElements IS NULL) THEN
1647 v_numElements := 0;
1648 END IF;
1649
1650 -- 2. Construct the sql statement
1651 v_stmt := 'SELECT ' || p_formula || ' FROM (SELECT ';
1652 FOR i IN 1..p_components.Count LOOP
1653 IF (i > 1) THEN
1654 v_stmt := v_stmt || ' ,';
1655 END IF;
1656 v_stmt := v_stmt || ':' || i || ' AS ID#' ||
1657 p_components(i).component_pk_key;
1658 END LOOP;
1659 v_stmt := v_stmt || ' FROM DUAL)';
1660
1661 c := DBMS_SQL.OPEN_CURSOR;
1662 DBMS_SQL.PARSE(c, v_stmt, DBMS_SQL.NATIVE);
1663
1664 -- 2. Calculate the virtual meter for each hour available
1665 FOR i IN 1..v_numElements LOOP
1666 v_usage_value := calVirtualMeter(c, p_components, i);
1667 v_hour_end_time := v_hour_start_time + 1/24 - 1/86400;
1668 INSERT INTO mth_meter_readings
1669 (meter_fk_key, from_time, to_time, usage_value,
1670 creation_date, last_update_date, creation_system_id,
1671 last_update_system_id, created_by, last_updated_by,
1672 last_update_login, processed_flag) VALUES
1673 (p_meter_fk_key, v_hour_start_time, v_hour_end_time,
1674 v_usage_value, SYSDATE, SYSDATE, v_system_id,
1675 v_system_id, v_system_id, v_system_id, v_system_id, 'N');
1676 v_hour_start_time := v_hour_start_time + 1/24;
1677
1678
1679 END LOOP;
1680 DBMS_SQL.CLOSE_CURSOR(c);
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 -- v_err_code := 'Failed to calculate the virtual meter between ' ||
1685 -- To_Char(p_start_time, 'YYYY-MM-DD HH24:MI:SS') || ' and ' ||
1686 -- To_Char(p_end_time, 'YYYY-MM-DD HH24:MI:SS') ||
1687 -- '. The error code is ' || SQLCODE ||
1691 INSERT INTO mth_runtime_err
1688 -- ' and the error message is ' || SQLERRM || '.';
1689 v_err_code := SQLCODE;
1690 v_err_msg := SQLERRM;
1692 ( MODULE, error_code, error_msg, timestamp) VALUES
1693 ('MTH_SUSTAIN_ASPECT_PKG.cal_save_virtual_meter',
1694 v_err_code, v_err_msg, SYSDATE);
1695
1696 DBMS_SQL.CLOSE_CURSOR(c);
1697
1698
1699 END cal_save_virtual_meter;
1700
1701
1702
1703
1704 /* ****************************************************************************
1705 * Procedure :ADD_VRT_TO_METER_READINGS *
1706 * Description :Calculate all the virtual meters if possible hourly and *
1707 * load the data into meter readings table MTH_METER_READINGS *
1708 * File Name :MTHSUSAB.PLS *
1709 * Visibility :Private *
1710 * Parameters :None *
1711 * Return Value :None *
1712 **************************************************************************** */
1713
1714 PROCEDURE ADD_VRT_MTS_TO_METER_READINGS (p_virtual_meter_start_date IN DATE
1715 DEFAULT VIRTUAL_METER_START_DATE)
1716 IS
1717 -- Get all the virtual meters order by precedence
1718 -- Fixed bug 9965797 to POPULATE THE METER READINGS FOR EACH METER ONLY ONCE
1719 -- by adding DISTINCT in the SELECT statement below
1720 CURSOR c_getVirtualMeters IS
1721 SELECT DISTINCT M.METER_PK_KEY, M.VIRTUAL_METER_TYPE,
1722 M.VIRTUAL_METER_FORMULA,
1723 M.PRECEDENCE, E.ENTITY_FK_KEY, UPPER(E.ENTITY_TYPE) ENTITY_TYPE,
1724 R.END_TIME
1725 FROM MTH_METERS M, MTH_METER_ENTITIES E,
1726 (SELECT meter_fk_key, Max(to_time) end_time
1727 FROM MTH_METER_READINGS
1728 GROUP BY meter_fk_key) R
1729 WHERE M.METER_TYPE = 'VRT' AND M.METER_PK_KEY = E.METER_FK_KEY (+) AND
1730 M.METER_PK_KEY = r.meter_fk_key (+)
1731 ORDER BY M.PRECEDENCE;
1732
1733 v_start_time DATE;
1734 v_end_time DATE;
1735 v_compoents component_lookup_type;
1736 v_num_trans NUMBER := 0;
1737
1738 BEGIN
1739 -- 1. Get all the virtual meters order by their precedence to
1740 -- to calculate the virtual meters
1741 v_end_time := trunc(SYSDATE, 'HH24');
1742 FOR v_meter IN c_getVirtualMeters LOOP
1743 -- 1.1 Calculate the virtual meter only if it has passed at least
1744 -- one hour from last reading
1745 --v_start_time := Nvl(v_meter.END_TIME, p_virtual_meter_start_date) + 1/2600;
1746 v_start_time := Nvl(v_meter.END_TIME, p_virtual_meter_start_date) + 1/23600;
1747 v_start_time := Trunc(v_start_time, 'HH24');
1748 IF (v_meter.END_TIME IS NOT NULL AND v_start_time <= v_meter.END_TIME) THEN
1749 v_start_time := v_start_time + 1/24;
1750 END IF;
1751 IF (v_end_time > trunc(v_start_time + 1/24, 'HH24')) THEN
1752
1753 v_compoents := cal_virtual_meter_component(v_meter.METER_PK_KEY,
1754 v_meter.VIRTUAL_METER_TYPE,
1755 v_meter.VIRTUAL_METER_FORMULA,
1756 v_meter.ENTITY_TYPE,
1757 v_meter.ENTITY_FK_KEY,
1758 v_start_time,
1759 v_end_time);
1760
1761
1762 END IF;
1763
1764 -- 1.2 Calculate and save the meter values
1765 cal_save_virtual_meter(v_meter.METER_PK_KEY,
1766 v_meter.VIRTUAL_METER_FORMULA,
1767 v_compoents,
1768 v_start_time,
1769 v_end_time);
1770 END LOOP;
1771
1772 END ADD_VRT_MTS_TO_METER_READINGS;
1773
1774 PROCEDURE init_hour_reading_table
1775 AS
1776
1777 BEGIN
1778 p_reading := MeterReadingTable(NULL,NULL,NULL,NULL,NULL);
1779 END init_hour_reading_table;
1780
1781 PROCEDURE init_shift_break_table
1782 AS
1783
1784 BEGIN
1785 p_shift := EntityShiftTable(NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1786 END init_shift_break_table;
1787
1788 FUNCTION break_hours RETURN MeterReadingTable
1789 AS
1790 CURSOR cur_meter
1791 IS
1792 SELECT a.meter_fk_key, a.from_time, a.to_time, a.usage_value, b.meter_code
1793 FROM mth_meter_readings a, mth_meters b
1794 WHERE a.meter_fk_key = b.meter_pk_key
1795 AND a.processed_flag = 'N'
1796 ORDER BY a.meter_fk_key, a.from_time;
1797
1798 l_hour_count NUMBER;
1799 i NUMBER := 1;
1800
1801 BEGIN
1802 init_hour_reading_table;
1803 FOR reading_rec in cur_meter LOOP
1804 --SELECT round ((reading_rec.to_time - reading_rec.from_time) *24)
1805 SELECT (Trunc(reading_rec.to_time, 'hh24') - Trunc(reading_rec.from_time, 'hh24')) *24
1806 INTO l_hour_count
1807 FROM DUAL;
1808
1809 FOR idx in 0..l_hour_count LOOP
1810 p_reading.EXTEND(1);
1811 BEGIN
1812 SELECT reading_rec.meter_fk_key,
1813 h.hour_pk_key,
1814 (CASE idx
1815 WHEN 0 THEN reading_rec.from_time
1816 ELSE h.from_time
1817 END) reading_from_time,
1818 (CASE idx
1819 WHEN l_hour_count THEN reading_rec.to_time
1820 ELSE h.to_time
1821 END) reading_to_time,
1822 (((CASE idx
1823 WHEN l_hour_count THEN reading_rec.to_time
1824 ELSE h.to_time
1828 ELSE h.from_time
1825 END) -
1826 (CASE idx
1827 WHEN 0 THEN reading_rec.from_time
1829 END))*24*60*60+1)
1830 /((reading_rec.to_time - reading_rec.from_time)*24*60*60+1)
1831 *reading_rec.usage_value
1832 INTO p_reading(i).meter_fk_key, p_reading(i).hour_pk_key, p_reading(i).from_time, p_reading(i).to_time, p_reading(i).usage_value
1833 FROM mth_hour_d h
1834 WHERE Trunc(reading_rec.from_time, 'hh24') + idx/24 = h.from_time;
1835 i := i+1;
1836
1837 EXCEPTION
1838 WHEN No_Data_Found THEN
1839 IF SQL%NOTFOUND THEN
1840 INSERT INTO mth_meter_readings_err
1841 (meter_readings_err_pk_key, meter_code, from_time, to_time, usage_value, tag_code, reprocess_ready_yn,
1842 err_code, creation_date, last_update_date)
1843 VALUES (MTH_METER_READINGS_ERR_S.NEXTVAL, reading_rec.meter_code, reading_rec.from_time, reading_rec.to_time,
1844 reading_rec.usage_value, NULL, 'N', 'HRNA', SYSDATE, SYSDATE);
1845 END IF;
1846 EXIT;
1847 WHEN OTHERS THEN
1848 ROLLBACK;
1849 raise_application_error(-20000,'Error occurs when inserting error table:'||SQLCODE||' -ERROR- '||SQLERRM);
1850 END;
1851
1852 END LOOP;
1853
1854 BEGIN
1855 UPDATE mth_meter_readings SET processed_flag = 'Y'
1856 WHERE meter_fk_key = reading_rec.meter_fk_key
1857 AND from_time = reading_rec.from_time;
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860 ROLLBACK;
1861 raise_application_error(-20001,'Error occurs when update process flag:'||SQLCODE||' -ERROR- '||SQLERRM);
1862 END;
1863 END LOOP;
1864
1865 RETURN p_reading;
1866
1867 END break_hours;
1868
1869 PROCEDURE break_reading
1870 AS
1871
1872 j NUMBER := 1;
1873 l NUMBER := 1;
1874 l_duration NUMBER;
1875 l_entity_fk_key mth_meter_entities.entity_fk_key%TYPE;
1876 l_shift_rec_to_time mth_meter_readings.to_time%TYPE;
1877 l_new_from_time mth_meter_readings.from_time%TYPE;
1878 l_to_time mth_meter_readings.to_time%TYPE;
1879
1880
1881 CURSOR cur_entities (l_meter_fk_key NUMBER)
1882 IS
1883 SELECT meter_fk_key, entity_fk_key
1884 FROM mth_meter_entities
1885 WHERE meter_fk_key = l_meter_fk_key
1886 AND status = 'ACTIVE';
1887
1888 CURSOR cur_shifts (l_meter_fk_key NUMBER, l_entity_fk_key NUMBER, l_from_time DATE, l_to_time DATE)
1889 IS
1890 SELECT l_meter_fk_key, l_entity_fk_key, shift_workday_fk_key,
1891 Greatest(from_date, l_from_time) from_time,
1892 least(l_to_time, To_Date) to_time
1893 FROM mth_equipment_shifts_d
1894 WHERE equipment_fk_key = l_entity_fk_key
1895 AND ( from_date <= l_from_time AND to_date >= l_from_time OR
1896 from_date <= l_to_time AND to_date >= l_to_time OR
1897 from_date >= l_from_time AND from_date <= l_to_time ) AND
1898 from_date IS NOT NULL AND To_Date IS NOT NULL AND
1899 from_date <> To_Date;
1900
1901
1902 CURSOR cur_catch_all_shifts (l_meter_fk_key NUMBER, l_entity_fk_key NUMBER, l_from_time DATE, l_to_time DATE)
1903 IS
1904 SELECT l_meter_fk_key, l_entity_fk_key, shift_workday_fk_key,
1905 l_from_time from_time,
1906 l_to_time to_time
1907 FROM mth_equipment_shifts_d
1908 WHERE equipment_fk_key = l_entity_fk_key
1909 AND Trunc(availability_date) = Trunc(l_from_time)
1910 AND Nvl(from_date, Trunc(availability_date)) = Nvl(To_Date, Trunc(availability_date));
1911
1912 -- Find catch all shifts from workday shifts
1913 CURSOR cur_workday_shifts (l_meter_fk_key NUMBER, l_entity_fk_key NUMBER, l_from_time DATE, l_to_time DATE)
1914 IS
1915 SELECT l_meter_fk_key, l_entity_fk_key, a.shift_workday_pk_key,
1916 l_from_time from_time,
1917 l_to_time to_time
1918 FROM mth_workday_shifts_d a,
1919 (SELECT plant_fk_key site_id, entity_pk_key
1920 FROM mth_equip_entities_mst
1921 UNION ALL
1922 SELECT plant_pk_key site_id, plant_pk_key entity_pk_key
1923 FROM mth_plants_d
1924 UNION ALL
1925 SELECT plant_fk_key site_id, resource_pk_key entity_pk_key
1926 FROM mth_resources_d
1927 UNION ALL
1928 SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key
1929 FROM mth_equipments_d
1930 ) b
1931 WHERE a.plant_fk_key = b.site_id
1932 AND b.entity_pk_key = l_entity_fk_key
1933 AND a.shift_workday_pk_key NOT IN (
1934 SELECT DISTINCT shift_workday_fk_key shift_key
1935 FROM mth_equipment_shifts_d
1936 WHERE equipment_fk_key = l_entity_fk_key)
1937 AND Trunc(a.shift_date) = Trunc(l_from_time)
1938 AND Nvl(a.from_date, Trunc(a.shift_date)) = Nvl(a.To_Date, Trunc(a.shift_date));
1939
1940 v_new_to_time DATE;
1941
1942 BEGIN
1943
1944 p_reading := break_hours;
1945 init_shift_break_table;
1946
1947 FOR i in p_reading.FIRST..p_reading.LAST LOOP
1948 FOR entity_rec IN cur_entities (p_reading(i).meter_fk_key) LOOP
1949 l_shift_rec_to_time := p_reading(i).from_time;
1950 l_new_from_time := p_reading(i).from_time;
1951 IF p_reading(i).from_time = p_reading(i).to_time THEN
1952 l_shift_rec_to_time := p_reading(i).from_time - 1/(24*60*60);
1953 END IF;
1954 l_to_time := p_reading(i).to_time;
1958 -- by adding one more second when calculating duration in the formula below.
1955 -- Fixed bug 9965814 - NEED TO REMOVE DATA ROUNDING LOGIC WHEN POPULATING DATA INTO ESA TABLE
1956 -- by removing the ROUND function below.
1957 -- Fixed bug 9965863 - ONE ALLOCATION LOGIC WHEN POPULATING DATA INTO ESA NEEDS ADD ONE SECOND
1959 -- l_duration := REPLACE( Round((p_reading(i).to_time - p_reading(i).from_time)*24*60*60), 0, 1);
1960 l_duration := ((p_reading(i).to_time - p_reading(i).from_time)*24*60*60 + 1);
1961
1962
1963 l_entity_fk_key := entity_rec.entity_fk_key;
1964 WHILE l_to_time > l_shift_rec_to_time
1965 LOOP
1966 BEGIN
1967 OPEN cur_shifts (p_reading(i).meter_fk_key, l_entity_fk_key, l_new_from_time, l_to_time);
1968 FETCH cur_shifts INTO p_shift(j).meter_fk_key, p_shift(j).entity_fk_key,
1969 p_shift(j).shift_workday_fk_key, p_shift(j).from_time, p_shift(j).to_time;
1970
1971 IF cur_shifts%NOTFOUND OR l_new_from_time < p_shift(j).from_time THEN
1972 v_new_to_time := CASE WHEN cur_shifts%NOTFOUND
1973 THEN l_to_time
1974 ELSE p_shift(j).from_time - 1/86400
1975 END;
1976 OPEN cur_catch_all_shifts (p_reading(i).meter_fk_key,
1977 l_entity_fk_key,
1978 l_new_from_time,
1979 v_new_to_time);
1980 FETCH cur_catch_all_shifts INTO p_shift(j).meter_fk_key, p_shift(j).entity_fk_key,
1981 p_shift(j).shift_workday_fk_key,
1982 p_shift(j).from_time, p_shift(j).to_time;
1983
1984 IF cur_catch_all_shifts%NOTFOUND THEN
1985 OPEN cur_workday_shifts (p_reading(i).meter_fk_key,
1986 l_entity_fk_key,
1987 l_new_from_time,
1988 v_new_to_time);
1989 FETCH cur_workday_shifts INTO p_shift(j).meter_fk_key, p_shift(j).entity_fk_key,
1990 p_shift(j).shift_workday_fk_key,
1991 p_shift(j).from_time, p_shift(j).to_time;
1992
1993 IF cur_workday_shifts%NOTFOUND THEN
1994 p_shift(j).meter_fk_key := p_reading(i).meter_fk_key;
1995 p_shift(j).entity_fk_key := l_entity_fk_key;
1996 p_shift(j).shift_workday_fk_key := -99999;
1997 p_shift(j).from_time := l_new_from_time;
1998 p_shift(j).to_time := v_new_to_time;
1999 END IF;
2000 CLOSE cur_workday_shifts;
2001 END IF;
2002 CLOSE cur_catch_all_shifts;
2003 END IF;
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006 raise_application_error(-20002,'Unknown Exception to allocate meter reading');
2007 END;
2008 CLOSE cur_shifts;
2009 p_shift(j).hour_fk_key := p_reading(i).hour_pk_key;
2010 -- Fixed bug 9965814 - NEED TO REMOVE DATA ROUNDING LOGIC WHEN POPULATING DATA INTO ESA TABLE
2011 -- by removing the ROUND function below.
2012 -- Fixed bug 9965863 - ONE ALLOCATION LOGIC WHEN POPULATING DATA INTO ESA NEEDS ADD ONE SECOND
2013 -- by adding one more second when calculating duration in the formula below.
2014 -- p_shift(j).usage_value := Round(REPLACE(Round((p_shift(j).to_time - p_shift(j).from_time)*24*60*60),0,1)/l_duration*p_reading(i).usage_value,2);
2015 p_shift(j).usage_value := ((p_shift(j).to_time - p_shift(j).from_time)*24*60*60 + 1)/l_duration*p_reading(i).usage_value;
2016 l_shift_rec_to_time := p_shift(j).to_time;
2017 l_new_from_time := p_shift(j).to_time + 1/(24*60*60);
2018 j := j + 1;
2019 p_shift.extend(1);
2020 END LOOP;
2021 END LOOP;
2022 END LOOP;
2023
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 ROLLBACK;
2027 raise_application_error(-20003,'Error occurs when break shifts:'||SQLCODE||' -ERROR- '||SQLERRM);
2028
2029 END break_reading;
2030
2031
2032 PROCEDURE load_sustain_emission_to_hour
2033 IS
2034
2035 BEGIN
2036
2037 INSERT INTO MTH_ENTITY_SUST_HR_EMISSIONS
2038 (esa_hr_fk_key, emission_code, emission_name, emission_quantity,
2039 emission_uom)
2040 SELECT Max(esa_pk_key) AS esa_hr_fk_key,
2041 e.emission_code,
2042 e.emission_name,
2043 sum(e.emission_quantity) AS emission_quantity,
2044 e.emission_uom
2045 FROM mth_entity_sustain_aspect a, mth_hour_d h,
2046 MTH_ENTITY_SUST_EMISSIONS e
2047 WHERE a.hour_fk_key = h.hour_pk_key AND e.esa_fk_key = a.esa_pk_key
2048 GROUP BY a.plant_fk_key, a.entity_fk_key, a.entity_type,
2049 a.sustain_aspect, a.usage_category, a.meter_fk_key,
2050 a.meter_category, h.from_time, h.to_time, a.hour_fk_key,
2051 a.shift_workday_fk_key, a.entity_name, a.entity_type_name,
2052 a.sustain_aspect_name, a.usage_category_name, a.meter_type,
2053 a.meter_type_name, a.meter_category_name,
2054 a.simulation_name, a.usage_uom, a.currency,
2055 e.emission_code, e.emission_name, e.emission_uom
2059 a.entity_name, a.entity_type_name,
2056 order BY a.entity_fk_key, a.entity_type, a.sustain_aspect,
2057 a.usage_category, a.meter_fk_key, a.meter_category,
2058 h.from_time, h.to_time, a.hour_fk_key,a.shift_workday_fk_key,
2060 a.sustain_aspect_name, a.usage_category_name, a.meter_type,
2061 a.meter_type_name, a.meter_category_name,
2062 a.simulation_name, a.usage_uom, a.currency,
2063 e.emission_code, e.emission_name, e.emission_uom;
2064
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 ROLLBACK;
2068 raise_application_error(-20005,
2069 'Error occurs when inserting MTH_ENTITY_SUST_HR_EMISSIONS table:'
2070 ||SQLCODE||' -ERROR- '||SQLERRM);
2071 END load_sustain_emission_to_hour;
2072
2073
2074 PROCEDURE load_sustain_aspect_to_hour
2075 IS
2076
2077 BEGIN
2078
2079 DELETE FROM MTH_ENTITY_SUST_HR_EMISSIONS;
2080 DELETE FROM mth_entity_sustain_aspect_hour;
2081
2082 INSERT INTO mth_entity_sustain_aspect_hour
2083 (esa_hr_pk_key, plant_fk_key, entity_fk_key, entity_type,
2084 sustain_aspect_fk_key,
2085 sustain_aspect, usage_category, meter_fk_key, meter_category,
2086 from_time, to_time, hour_fk_key, shift_workday_fk_key,
2087 entity_name, entity_type_name,
2088 sustain_aspect_name, usage_category_name, meter_type,
2089 meter_type_name, meter_category_name,
2090 simulation_name, usage_value, usage_uom, usage_cost,
2091 currency, creation_date, last_update_date)
2092 SELECT max(esa_pk_key) AS esa_hr_pk_key,
2093 a.plant_fk_key, a.entity_fk_key, a.entity_type,
2094 a.sustain_aspect_fk_key,
2095 a.sustain_aspect, a.usage_category, a.meter_fk_key,
2096 a.meter_category, h.from_time, h.to_time, a.hour_fk_key,
2097 a.shift_workday_fk_key, a.entity_name, a.entity_type_name,
2098 a.sustain_aspect_name, a.usage_category_name, a.meter_type,
2099 a.meter_type_name, a.meter_category_name,
2100 a.simulation_name, Sum(a.usage_value) AS usage_value,
2101 a.usage_uom, Sum(a.usage_cost) AS usage_cost,
2102 a.currency, SYSDATE AS creation_date,
2103 SYSDATE AS last_update_date
2104 FROM mth_entity_sustain_aspect a, mth_hour_d h
2105 WHERE a.hour_fk_key = h.hour_pk_key
2106 GROUP BY a.plant_fk_key, a.entity_fk_key, a.entity_type,
2107 a.sustain_aspect, a.sustain_aspect_fk_key, a.usage_category, a.meter_fk_key,
2108 a.meter_category, h.from_time, h.to_time, a.hour_fk_key,
2109 a.shift_workday_fk_key, a.entity_name, a.entity_type_name,
2110 a.sustain_aspect_name, a.usage_category_name, a.meter_type,
2111 a.meter_type_name, a.meter_category_name,
2112 a.simulation_name, a.usage_uom, a.currency
2113 order BY a.entity_fk_key, a.entity_type, a.sustain_aspect,
2114 a.sustain_aspect_fk_key,
2115 a.usage_category, a.meter_fk_key, a.meter_category,
2116 h.from_time, h.to_time, a.hour_fk_key,a.shift_workday_fk_key,
2117 a.entity_name, a.entity_type_name,
2118 a.sustain_aspect_name, a.usage_category_name, a.meter_type,
2119 a.meter_type_name, a.meter_category_name,
2120 a.simulation_name, a.usage_uom, a.currency;
2121
2122 load_sustain_emission_to_hour;
2123
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 ROLLBACK;
2127 raise_application_error(-20005,
2128 'Error occurs when inserting MTH_ENTITY_SUSTAIN_ASPECT_HOUR table:'
2129 ||SQLCODE||' -ERROR- '||SQLERRM);
2130 END load_sustain_aspect_to_hour;
2131
2132
2133
2134 /* ****************************************************************************
2135 * Procedure :load_data_to_sustain_emissions *
2136 * Description :Load data into the child table MTH_ENTITY_SUST_EMISSIONS *
2137 * File Name :MTHSUSAB.PLS *
2138 * Visibility :Private *
2139 * Parameters :None *
2140 * Return Value :None *
2141 **************************************************************************** */
2142
2143 PROCEDURE load_data_to_sustain_emissions
2144 AS
2145
2146 l_start_time DATE;
2147 l_end_time DATE;
2148 l_count NUMBER;
2149
2150 BEGIN
2151
2152 FOR i IN p_shift.FIRST..p_shift.LAST LOOP
2153 INSERT INTO MTH_ENTITY_SUST_EMISSIONS
2154 (esa_fk_key, emission_code, emission_name, emission_quantity,
2155 emission_uom)
2156 SELECT p_shift(i).esa_pk_key,
2157 y.emission_code,
2158 (SELECT meaning FROM fnd_lookup_values_vl
2159 WHERE lookup_type = 'MTH_SUSTAIN_EMISSION'
2160 AND lookup_code = y.emission_code) emission_name,
2161 p_shift(i).usage_value *
2162 y.average_emission_factor AS emission_quantity,
2163 (SELECT emission_uom FROM mth_sustain_emissions
2164 WHERE sustain_aspect_fk_key = w.sustain_aspect_pk_key
2165 AND emission_code = y.emission_code) emission_uom
2166 FROM MTH_METER_ENTITIES x, mth_meters x1,
2167 (SELECT m.plant_fk_key, m.effective_date, m.expiration_date, m.sustain_aspect_fk_key,
2168 m.average_planned_cost, n.emission_code, Nvl(n.average_emission_factor,0) average_emission_factor
2169 FROM MTH_SITE_SUSTAINABILITIES m,
2170 (SELECT a.site_sustain_pk_key, c.sustain_emission_fk_key, d.emission_code,
2174 WHERE a.site_sustain_pk_key = b.site_sustain_fk_key
2171 sum(b.planned_usage_percentage/100 * Nvl(c.emission_factor,0)) average_emission_factor
2172 FROM MTH_SITE_SUSTAINABILITIES a, MTH_SITE_SUSTAIN_SOURCES b,
2173 MTH_SOURCE_EMISSION_FACTORS c, MTH_SUSTAIN_EMISSIONS d
2175 AND b.site_sustain_source_pk_key = c.site_sustain_source_fk_key
2176 AND d.sustain_emission_pk_key = c.sustain_emission_fk_key
2177 AND a.sustain_aspect_fk_key = d.sustain_aspect_fk_key
2178 AND d.status = 'ACTIVE'
2179 AND p_shift(i).to_time BETWEEN a.effective_date AND Nvl(a.expiration_date, SYSDATE)
2180 GROUP by a.site_sustain_pk_key, c.sustain_emission_fk_key, d.emission_code
2181 ) n
2182 WHERE m.site_sustain_pk_key = n.site_sustain_pk_key
2183 AND p_shift(i).to_time BETWEEN m.effective_date AND Nvl(m.expiration_date, SYSDATE)
2184 ) y,
2185 (SELECT f.*, g.meaning entity_type_name
2186 FROM mth_all_entities_v f, fnd_lookup_values_vl g
2187 WHERE Upper(f.entity_type) = g.lookup_code
2188 AND (g.lookup_type = 'MTH_USER_DEFINED_ENTITIES' OR
2189 g.lookup_type = 'MTH_OTHER_ENTITY_TYPE')
2190 ) z,
2191 (SELECT f.sustain_aspect_pk_key, f.sustain_aspect_code,
2192 f.usage_uom, g.meaning
2193 FROM mth_sustain_aspects f, fnd_lookup_values_vl g
2194 WHERE f.sustain_aspect_code = g.lookup_code
2195 AND g.lookup_type = 'MTH_SUSTAIN_ASPECT'
2196 ) w
2197 WHERE x.STATUS = 'ACTIVE'
2198 AND x.meter_fk_key = x1.meter_pk_key
2199 AND x.entity_fk_key = z.entity_pk_key
2200 AND x.entity_type = Upper(z.entity_type)
2201 AND y.plant_fk_key = z.site_id
2202 AND w.sustain_aspect_pk_key = y.sustain_aspect_fk_key
2203 AND w.sustain_aspect_pk_key = x1.sustain_aspect_fk_key
2204 AND x.meter_fk_key = p_shift(i).meter_fk_key
2205 AND x.entity_fk_key = p_shift(i).entity_fk_key;
2206 END LOOP;
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 ROLLBACK;
2210 raise_application_error(-20004,
2211 'Error occurs when inserting data into MTH_ENTITY_SUST_EMISSIONS table:'
2212 || SQLCODE||' -ERROR- '||SQLERRM);
2213
2214 END load_data_to_sustain_emissions;
2215
2216
2217
2218
2219 PROCEDURE load_reading_to_sustain_aspect
2220 AS
2221
2222 l_start_time DATE;
2223 l_end_time DATE;
2224 l_count NUMBER;
2225
2226 BEGIN
2227 init_hour_reading_table;
2228 init_shift_break_table;
2229 break_reading;
2230
2231 -- Fill the esa_pk_key
2232 FOR i IN p_shift.FIRST..p_shift.LAST LOOP
2233 SELECT MTH_ENTITY_SUSTAIN_ASPECT_S.NEXTVAL
2234 INTO p_shift(i).esa_pk_key
2235 FROM dual;
2236 END LOOP;
2237
2238
2239 FOR i IN p_shift.FIRST..p_shift.LAST LOOP
2240 INSERT INTO mth_entity_sustain_aspect
2241 (esa_pk_key, plant_fk_key, entity_fk_key, entity_type,
2242 sustain_aspect_fk_key,
2243 sustain_aspect, usage_category, meter_fk_key, meter_category,
2244 from_time, to_time, hour_fk_key, shift_workday_fk_key,
2245 entity_name, entity_type_name,
2246 sustain_aspect_name, usage_category_name, meter_type,
2247 meter_type_name, meter_category_name,
2248 simulation_name, usage_value, usage_uom, usage_cost,
2249 currency, creation_date, last_update_date)
2250 SELECT p_shift(i).esa_pk_key, y.plant_fk_key, x.entity_fk_key,
2251 z.entity_type, w.sustain_aspect_pk_key, w.sustain_aspect_code,
2252 Nvl(x.usage_category_code, -99999), x.meter_fk_key,
2253 Nvl(x.meter_category_code, -99999),
2254 p_shift(i).from_time, p_shift(i).to_time,
2255 p_shift(i).hour_fk_key, p_shift(i).shift_workday_fk_key,
2256 z.entity_name, z.entity_type_name,
2257 w.meaning, (SELECT meaning FROM fnd_lookup_values_vl
2258 WHERE lookup_type = 'MTH_ENERGY_USAGE_CATEGORIES'
2259 AND lookup_code = x.usage_category_code) usage_category_name,
2260 x1.meter_type, (SELECT meaning FROM fnd_lookup_values_vl
2261 WHERE lookup_type = 'MTH_METER_TYPE'
2262 AND lookup_code = x1.meter_type) meter_type_name,
2263 (SELECT meaning FROM fnd_lookup_values_vl
2264 WHERE lookup_type = 'MTH_METER_CATEGORY'
2265 AND lookup_code = x.meter_category_code) meter_category_name,
2266 (SELECT meaning FROM fnd_lookup_values_vl
2267 WHERE lookup_type = 'MTH_SIMULATION_NAME'
2268 AND lookup_code = x.simulation_name_code) simulation_name,
2269 p_shift(i).usage_value,
2270 w.usage_uom,
2271 p_shift(i).usage_value * y.average_planned_cost,
2272 (SELECT i.currency_code FROM mth_plants_d i
2273 WHERE i.plant_pk_key = z.site_id) currency,
2274 SYSDATE, SYSDATE
2275 FROM MTH_METER_ENTITIES x, mth_meters x1,
2276 (SELECT m.plant_fk_key, m.effective_date, m.expiration_date, m.sustain_aspect_fk_key,
2277 m.average_planned_cost
2278 FROM MTH_SITE_SUSTAINABILITIES m
2279 WHERE p_shift(i).to_time BETWEEN m.effective_date AND Nvl(m.expiration_date, SYSDATE)
2280 ) y,
2281 (SELECT f.*, g.meaning entity_type_name
2282 FROM mth_all_entities_v f, fnd_lookup_values_vl g
2283 WHERE Upper(f.entity_type) = g.lookup_code
2287 (SELECT f.sustain_aspect_pk_key, f.sustain_aspect_code,
2284 AND (g.lookup_type = 'MTH_USER_DEFINED_ENTITIES' OR
2285 g.lookup_type = 'MTH_OTHER_ENTITY_TYPE')
2286 ) z,
2288 f.usage_uom, g.meaning
2289 FROM mth_sustain_aspects f, fnd_lookup_values_vl g
2290 WHERE f.sustain_aspect_code = g.lookup_code
2291 AND g.lookup_type = 'MTH_SUSTAIN_ASPECT'
2292 ) w
2293 WHERE x.STATUS = 'ACTIVE'
2294 AND x.meter_fk_key = x1.meter_pk_key
2295 AND x.entity_fk_key = z.entity_pk_key
2296 AND x.entity_type = Upper(z.entity_type)
2297 AND y.plant_fk_key = z.site_id
2298 AND w.sustain_aspect_pk_key = y.sustain_aspect_fk_key
2299 AND w.sustain_aspect_pk_key = x1.sustain_aspect_fk_key
2300 AND x.meter_fk_key = p_shift(i).meter_fk_key
2301 AND x.entity_fk_key = p_shift(i).entity_fk_key;
2302 end loop;
2303
2304 -- Load emission data into MTH_ENTITY_SUST_EMISSIONS child table
2305 load_data_to_sustain_emissions;
2306
2307 load_sustain_aspect_to_hour;
2308
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 ROLLBACK;
2312 raise_application_error(-20004,
2313 'Error occurs when inserting MTH_ENTITY_SUSTAIN_ASPECT table:'
2314 ||SQLCODE||' -ERROR- '||SQLERRM);
2315
2316 END load_reading_to_sustain_aspect;
2317
2318
2319
2320 /* ****************************************************************************
2321 * Procedure :truncate_entity_sustain_data *
2322 * Description :Truncate sustainability aspect tables and set the *
2323 * production performance table to be null. *
2324 * File Name :MTHSUSAB.PLS *
2325 * Visibility :Private *
2326 * Parameters :None *
2327 * Return Value :None *
2328 **************************************************************************** */
2329 PROCEDURE truncate_entity_sustain_data
2330 IS
2331 BEGIN
2332 mth_util_pkg.mth_truncate_table('MTH_TAG_METER_READINGS_LATEST');
2333 mth_util_pkg.mth_truncate_table('MTH_METER_READINGS');
2334 mth_util_pkg.mth_truncate_table('MTH_METER_READINGS_ERR');
2335 mth_util_pkg.mth_truncate_table('MTH_ENTITY_SUST_EMISSIONS');
2336 mth_util_pkg.mth_truncate_table('MTH_ENTITY_SUSTAIN_ASPECT');
2337 mth_util_pkg.mth_truncate_table('MTH_ENTITY_SUST_HR_EMISSIONS');
2338 mth_util_pkg.mth_truncate_table('MTH_ENTITY_SUSTAIN_ASPECT_HOUR');
2339 mth_util_pkg.mth_truncate_table('MTH_EQUIP_PROD_SUSTAIN_F');
2340
2341 END truncate_entity_sustain_data;
2342
2343 PROCEDURE process_entity_sustain_aspect
2344 (p_err_buff out NOCOPY VARCHAR2,
2345 p_retcode out NOCOPY NUMBER,
2346 p_process_mode IN VARCHAR2 DEFAULT 'INCR',
2347 p_virtual_meter_start_date_str IN varchar2 )
2348 IS
2349 v_raw_tab_name VARCHAR2(30) := 'MTH_TAG_METER_READINGS_RAW';
2350 v_curr_partition NUMBER;
2351 v_virtual_meter_start_date date;
2352 v_process_mode VARCHAR2(200);
2353 BEGIN
2354
2355 v_process_mode := p_process_mode;
2356 IF (v_process_mode IS NULL OR length(v_process_mode) = 0) THEN
2357 v_process_mode := 'INCR';
2358 END IF;
2359
2360 IF (v_process_mode = 'INIT') THEN
2361 truncate_entity_sustain_data;
2362 END IF;
2363
2364 IF (v_process_mode = 'INCR' OR v_process_mode = 'INIT') THEN
2365
2366 v_virtual_meter_start_date :=
2367 NVL(fnd_date.canonical_to_date(p_virtual_meter_start_date_str),trunc(sysdate));
2368
2369
2370 mth_util_pkg.switch_column_default_value(v_raw_tab_name, v_curr_partition);
2371
2372 IF (v_curr_partition = 1 OR v_curr_partition = 2) THEN
2373 LOAD_ACT_METER_RAW_TO_READINGS( v_curr_partition );
2374
2375 mth_util_pkg.truncate_table_partition (v_raw_tab_name, v_curr_partition);
2376 END IF;
2377
2378
2379 ADD_VRT_MTS_TO_METER_READINGS (v_virtual_meter_start_date);
2380
2381 COMMIT;
2382 load_reading_to_sustain_aspect;
2383 COMMIT;
2384
2385 fnd_file.put_line(FND_FILE.LOG,
2386 'Processing and populating data into entity sustainability aspect completed successfully');
2387 p_err_buff :=
2388 'Processing and populating data into entity sustainability aspect completed successfully';
2389 p_retcode := 0;
2390 ELSE
2391 p_retcode := 1;
2392 fnd_file.put_line(FND_FILE.LOG,
2393 'The process mode can only be INIT or INCR for initial and incremental respectively.'
2394 );
2395 fnd_file.put_line(FND_FILE.LOG,-20005);
2396 p_err_buff :=
2397 'The process mode can only be INIT or INCR for initial and incremental respectively.';
2398 END IF;
2399
2400 EXCEPTION
2401 WHEN OTHERS THEN
2402 ROLLBACK;
2403 p_retcode := 1;
2404 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
2405 fnd_file.put_line(FND_FILE.LOG,sqlcode);
2406 p_err_buff := substr(sqlerrm,1,240);
2407 --RAISE;
2408
2409
2410
2411 END process_entity_sustain_aspect;
2412
2413 END MTH_SUSTAIN_ASPECT_PKG;
[Home] [Help]