[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Procedure :insert_row_to_err_tab *
* Description :Insert the error row into the error with error code *
* File Name :MTHSUSAB.PLS *
* Visibility :Private *
* Parameters :p_tag_code - Tag code *
* p_reading_time - Reading time *
* p_tag_value - tag value *
* p_err_code - Error codes *
* Return Value :None *
**************************************************************************** */
PROCEDURE insert_row_to_err_tab(P_TAG_CODE IN VARCHAR2,
P_READING_TIME IN DATE,
P_TAG_VALUE IN NUMBER,
P_ERROR_CODE IN VARCHAR2)
IS
v_reprocess_ready_yn VARCHAR2(1) := 'N';
INSERT INTO MTH_METER_READINGS_ERR
(METER_READINGS_ERR_PK_KEY, TO_TIME, USAGE_VALUE, TAG_CODE,
REPROCESS_READY_YN, ERR_CODE)
VALUES (MTH_METER_READINGS_ERR_S.NEXTVAL, P_READING_TIME,
P_TAG_VALUE, P_TAG_CODE, v_reprocess_ready_yn, P_ERROR_CODE);
END insert_row_to_err_tab;
* Procedure :insert_runtime_error *
* Description :Insert runtime exception into mth_runtime_err table *
* File Name :MTHSUSAB.PLS *
* Visibility :Private *
* Parameters :p_proc_func_name - Name of the calling procedure or *
* function *
* p_error_code - Oracle error code *
* p_error_msg - error message *
* Return Value :None *
**************************************************************************** */
PROCEDURE insert_runtime_error(p_proc_func_name IN VARCHAR2,
p_error_code IN NUMBER,
p_error_msg IN VARCHAR2)
IS
v_module_name VARCHAR2(80);
INSERT INTO mth_runtime_err
( MODULE, error_code, error_msg, timestamp) VALUES
(v_module_name, p_error_code, p_error_msg, SYSDATE);
END insert_runtime_error;
* Procedure :insert_act_meters_to_readings *
* Description :Insert the actual meters associated with that tag *
* into meter readings table *
* File Name :MTHSUSAB.PLS *
* Visibility :Private *
* Parameters :p_tag_code - tag code *
* p_reading_time - tag reading time *
* p_incr_tag_value - incremental tag reading *
* p_prev_reading_time - reading time for previous one *
* p_frequency - frequeycy ; null if not incremental *
PROCEDURE insert_act_meters_to_readings(p_tag_code IN VARCHAR2,
p_reading_time IN DATE,
p_incr_tag_value IN NUMBER,
p_prev_reading_time IN DATE,
p_frequency IN NUMBER,
p_meter_keys_arr IN DBMS_SQL.NUMBER_TABLE)
IS
v_from_time DATE;
INSERT INTO MTH_METER_READINGS
(METER_FK_KEY, FROM_TIME, TO_TIME, USAGE_VALUE, PROCESSED_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, CREATION_SYSTEM_ID,
LAST_UPDATE_SYSTEM_ID) VALUES
(p_meter_keys_arr(i), v_from_time, p_reading_time,
p_incr_tag_value, v_processed_flag, SYSDATE, SYSDATE,
v_system_id, v_system_id);
END insert_act_meters_to_readings;
* Description :Update the latest reading time and tag value *
* for a tag if table MTH_TAG_METER_READINGS_LATEST already *
* has a entry for the tag. Otherwise, insert a new row *
* File Name :MTHSUSAB.PLS *
* Visibility :Private *
* Parameters :p_tag_code - tag code *
* p_latest_reading_time - reading time of the latest *
* p_latest_tag_value - latest tag reading *
* p_lookup_entry_exist - whether the entry with the *
* same tag code exists in the *
* MTH_TAG_METER_READINGS_LATEST or not *
* Return Value :None *
**************************************************************************** */
PROCEDURE upsert_tag_to_latest_tab(p_tag_code IN VARCHAR2,
p_latest_reading_time IN DATE,
p_latest_tag_value IN NUMBER,
p_lookup_entry_exist IN BOOLEAN)
IS
BEGIN
-- If the entry exists, do the update; otherwise, do the insert
UPDATE MTH_TAG_METER_READINGS_LATEST
SET reading_time = p_latest_reading_time, tag_value = p_latest_tag_value
WHERE tag_code = p_tag_code;
INSERT INTO MTH_TAG_METER_READINGS_LATEST
(TAG_CODE, READING_TIME, TAG_VALUE) VALUES
(p_tag_code, p_latest_reading_time, p_latest_tag_value);
SELECT R.TAG_CODE, R.READING_TIME, R.TAG_VALUE,
Decode(DATA_TYPE, 'NUM', 1, 0) IS_NUMBER,
CASE WHEN Nvl(S.APPLY_INCREMENTAL_LOGIC, 'N') = 'Y' AND
T.READING_TYPE = 'CHNG'
THEN 1
ELSE 0
END AS IS_CUMULATIVE,
Decode(T.ORDER_TYPE, 'ASC', 1, 0) IS_ASSENDING,
T.INITIAL_VALUE, T.MAX_RESET_VALUE, S.TAG_TYPE,
T.FREQUENCY_IN_MINUTES / 1440 as FREQUENCY
FROM MTH_TAG_METER_READINGS_RAW R, MTH_TAG_MASTER T,
MTH_TAG_DATA_SOURCES S
WHERE
R.TAG_CODE = T.TAG_CODE (+) AND
'ACTIVE' = T.STATUS (+) AND
T.TAG_DATA_SOURCE_FK_KEY = S.TAG_DATA_SOURCE_PK_KEY (+) AND
R.PROCESSING_FLAG = p_processing_flag
ORDER BY TAG_CODE, READING_TIME;
SELECT METER_PK_KEY
FROM MTH_METERS
WHERE TAG_CODE = p_tag_code AND meter_type = 'ACT';
SELECT TAG_VALUE, READING_TIME
FROM MTH_TAG_METER_READINGS_LATEST
WHERE TAG_CODE = p_tag_code;
v_num_insert_update NUMBER := 0;
v_num_insert_update := v_num_insert_update + 1;
INSERT INTO MTH_METER_READINGS_ERR
(METER_READINGS_ERR_PK_KEY, TO_TIME, USAGE_VALUE, TAG_CODE,
REPROCESS_READY_YN, ERR_CODE)
VALUES (MTH_METER_READINGS_ERR_S.NEXTVAL, r_raw_data.READING_TIME,
r_raw_data.TAG_VALUE, r_raw_data.TAG_CODE,
v_reprocess_ready_yn, v_err_code);
v_num_insert_update := v_num_insert_update + 1;
insert_act_meters_to_readings(r_raw_data.TAG_CODE,
r_raw_data.READING_TIME,
v_incr_tag_value,
v_prev_reading_time,
CASE r_raw_data.IS_CUMULATIVE
WHEN 0 THEN r_raw_data.FREQUENCY
ELSE NULL END,
v_meter_fk_key_array);
v_num_insert_update := v_num_insert_update + v_meter_fk_key_array.Count;
insert_runtime_error('insert_act_meters_to_readings',
v_error_code,
v_error_msg);
SELECT power_rating INTO v_power_rating
FROM mth_equipments_d
WHERE equipment_pk_key = p_entity_fk_key;
SELECT from_date, To_Date, availability_flag
FROM MTH_EQUIPMENT_SHIFTS_D
WHERE equipment_fk_key = p_entity_fk_key AND
UPPER(entity_type) = p_entity_type AND
To_Date >= p_from_time AND
from_date <= p_to_time
ORDER BY from_date;
SELECT actual_from_date, actual_to_date, run_hours, down_hours,
idle_hours, off_hours
FROM MTH_EQUIP_PROD_PERFORMANCE_F
WHERE equipment_fk_key = p_equip_fk_key AND
actual_to_date >= p_from_time AND
actual_from_date <= p_to_time AND
actual_to_date IS NOT NULL
ORDER BY actual_from_date;
SELECT from_time, to_time, usage_value
FROM mth_meter_readings
WHERE meter_fk_key = p_meter_fk_key AND
to_time >= p_from_time AND
from_time <= p_to_time
ORDER BY from_time;
SELECT DESCRIPTION
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = 'CUSTOM_SUSTAIN_ASPECT_API' AND
LOOKUP_TYPE ='MTH_CUSTOM_PLSQL_API';
SELECT DESCRIPTION INTO v_api_name
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = 'CUSTOM_SUSTAIN_ASPECT_API' AND
LOOKUP_TYPE ='MTH_CUSTOM_PLSQL_API';
INSERT INTO mth_runtime_err
( MODULE, error_msg, timestamp) VALUES
('MTH_SUSTAIN_ASPECT_PKG.getCompValuesForCustom', v_err_msg, SYSDATE);
v_stmt := 'SELECT ' || v_api_name || '(:1, ' || -- p_entity_type
':2, ' || -- p_entity_type
':3, ' || -- p_component_value
':4, ' || -- p_from_time
':5)' || -- p_to_time
' FROM DUAL';
INSERT INTO mth_runtime_err
( MODULE, error_code, error_msg, timestamp) VALUES
('MTH_SUSTAIN_ASPECT_PKG.getCompValuesForCustom',
v_err_code, v_err_msg, SYSDATE);
SELECT virtual_meter_component_pk_key as component_pk_key,
component_type, component_value
FROM MTH_VIRTUAL_METER_COMPONENTS
WHERE meter_fk_key = p_meter_fk_key;
* p_num_trans - current number of insert/update *
* Return Value :None *
**************************************************************************** */
PROCEDURE cal_save_virtual_meter(p_meter_fk_key IN NUMBER,
p_formula IN VARCHAR2,
p_components IN component_lookup_type,
p_start_time IN DATE,
p_end_time IN DATE)
IS
v_numElements NUMBER := NULL;
v_stmt := 'SELECT ' || p_formula || ' FROM (SELECT ';
INSERT INTO mth_meter_readings
(meter_fk_key, from_time, to_time, usage_value,
creation_date, last_update_date, creation_system_id,
last_update_system_id, created_by, last_updated_by,
last_update_login, processed_flag) VALUES
(p_meter_fk_key, v_hour_start_time, v_hour_end_time,
v_usage_value, SYSDATE, SYSDATE, v_system_id,
v_system_id, v_system_id, v_system_id, v_system_id, 'N');
INSERT INTO mth_runtime_err
( MODULE, error_code, error_msg, timestamp) VALUES
('MTH_SUSTAIN_ASPECT_PKG.cal_save_virtual_meter',
v_err_code, v_err_msg, SYSDATE);
SELECT DISTINCT M.METER_PK_KEY, M.VIRTUAL_METER_TYPE,
M.VIRTUAL_METER_FORMULA,
M.PRECEDENCE, E.ENTITY_FK_KEY, UPPER(E.ENTITY_TYPE) ENTITY_TYPE,
R.END_TIME
FROM MTH_METERS M, MTH_METER_ENTITIES E,
(SELECT meter_fk_key, Max(to_time) end_time
FROM MTH_METER_READINGS
GROUP BY meter_fk_key) R
WHERE M.METER_TYPE = 'VRT' AND M.METER_PK_KEY = E.METER_FK_KEY (+) AND
M.METER_PK_KEY = r.meter_fk_key (+)
ORDER BY M.PRECEDENCE;
SELECT a.meter_fk_key, a.from_time, a.to_time, a.usage_value, b.meter_code
FROM mth_meter_readings a, mth_meters b
WHERE a.meter_fk_key = b.meter_pk_key
AND a.processed_flag = 'N'
ORDER BY a.meter_fk_key, a.from_time;
SELECT (Trunc(reading_rec.to_time, 'hh24') - Trunc(reading_rec.from_time, 'hh24')) *24
INTO l_hour_count
FROM DUAL;
SELECT reading_rec.meter_fk_key,
h.hour_pk_key,
(CASE idx
WHEN 0 THEN reading_rec.from_time
ELSE h.from_time
END) reading_from_time,
(CASE idx
WHEN l_hour_count THEN reading_rec.to_time
ELSE h.to_time
END) reading_to_time,
(((CASE idx
WHEN l_hour_count THEN reading_rec.to_time
ELSE h.to_time
END) -
(CASE idx
WHEN 0 THEN reading_rec.from_time
ELSE h.from_time
END))*24*60*60+1)
/((reading_rec.to_time - reading_rec.from_time)*24*60*60+1)
*reading_rec.usage_value
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
FROM mth_hour_d h
WHERE Trunc(reading_rec.from_time, 'hh24') + idx/24 = h.from_time;
INSERT INTO mth_meter_readings_err
(meter_readings_err_pk_key, meter_code, from_time, to_time, usage_value, tag_code, reprocess_ready_yn,
err_code, creation_date, last_update_date)
VALUES (MTH_METER_READINGS_ERR_S.NEXTVAL, reading_rec.meter_code, reading_rec.from_time, reading_rec.to_time,
reading_rec.usage_value, NULL, 'N', 'HRNA', SYSDATE, SYSDATE);
raise_application_error(-20000,'Error occurs when inserting error table:'||SQLCODE||' -ERROR- '||SQLERRM);
UPDATE mth_meter_readings SET processed_flag = 'Y'
WHERE meter_fk_key = reading_rec.meter_fk_key
AND from_time = reading_rec.from_time;
raise_application_error(-20001,'Error occurs when update process flag:'||SQLCODE||' -ERROR- '||SQLERRM);
SELECT meter_fk_key, entity_fk_key
FROM mth_meter_entities
WHERE meter_fk_key = l_meter_fk_key
AND status = 'ACTIVE';
SELECT l_meter_fk_key, l_entity_fk_key, shift_workday_fk_key,
Greatest(from_date, l_from_time) from_time,
least(l_to_time, To_Date) to_time
FROM mth_equipment_shifts_d
WHERE equipment_fk_key = l_entity_fk_key
AND ( from_date <= l_from_time AND to_date >= l_from_time OR
from_date <= l_to_time AND to_date >= l_to_time OR
from_date >= l_from_time AND from_date <= l_to_time ) AND
from_date IS NOT NULL AND To_Date IS NOT NULL AND
from_date <> To_Date;
SELECT l_meter_fk_key, l_entity_fk_key, shift_workday_fk_key,
l_from_time from_time,
l_to_time to_time
FROM mth_equipment_shifts_d
WHERE equipment_fk_key = l_entity_fk_key
AND Trunc(availability_date) = Trunc(l_from_time)
AND Nvl(from_date, Trunc(availability_date)) = Nvl(To_Date, Trunc(availability_date));
SELECT l_meter_fk_key, l_entity_fk_key, a.shift_workday_pk_key,
l_from_time from_time,
l_to_time to_time
FROM mth_workday_shifts_d a,
(SELECT plant_fk_key site_id, entity_pk_key
FROM mth_equip_entities_mst
UNION ALL
SELECT plant_pk_key site_id, plant_pk_key entity_pk_key
FROM mth_plants_d
UNION ALL
SELECT plant_fk_key site_id, resource_pk_key entity_pk_key
FROM mth_resources_d
UNION ALL
SELECT plant_fk_key site_id, equipment_pk_key entity_pk_key
FROM mth_equipments_d
) b
WHERE a.plant_fk_key = b.site_id
AND b.entity_pk_key = l_entity_fk_key
AND a.shift_workday_pk_key NOT IN (
SELECT DISTINCT shift_workday_fk_key shift_key
FROM mth_equipment_shifts_d
WHERE equipment_fk_key = l_entity_fk_key)
AND Trunc(a.shift_date) = Trunc(l_from_time)
AND Nvl(a.from_date, Trunc(a.shift_date)) = Nvl(a.To_Date, Trunc(a.shift_date));
INSERT INTO MTH_ENTITY_SUST_HR_EMISSIONS
(esa_hr_fk_key, emission_code, emission_name, emission_quantity,
emission_uom)
SELECT Max(esa_pk_key) AS esa_hr_fk_key,
e.emission_code,
e.emission_name,
sum(e.emission_quantity) AS emission_quantity,
e.emission_uom
FROM mth_entity_sustain_aspect a, mth_hour_d h,
MTH_ENTITY_SUST_EMISSIONS e
WHERE a.hour_fk_key = h.hour_pk_key AND e.esa_fk_key = a.esa_pk_key
GROUP BY a.plant_fk_key, a.entity_fk_key, a.entity_type,
a.sustain_aspect, a.usage_category, a.meter_fk_key,
a.meter_category, h.from_time, h.to_time, a.hour_fk_key,
a.shift_workday_fk_key, a.entity_name, a.entity_type_name,
a.sustain_aspect_name, a.usage_category_name, a.meter_type,
a.meter_type_name, a.meter_category_name,
a.simulation_name, a.usage_uom, a.currency,
e.emission_code, e.emission_name, e.emission_uom
order BY a.entity_fk_key, a.entity_type, a.sustain_aspect,
a.usage_category, a.meter_fk_key, a.meter_category,
h.from_time, h.to_time, a.hour_fk_key,a.shift_workday_fk_key,
a.entity_name, a.entity_type_name,
a.sustain_aspect_name, a.usage_category_name, a.meter_type,
a.meter_type_name, a.meter_category_name,
a.simulation_name, a.usage_uom, a.currency,
e.emission_code, e.emission_name, e.emission_uom;
'Error occurs when inserting MTH_ENTITY_SUST_HR_EMISSIONS table:'
||SQLCODE||' -ERROR- '||SQLERRM);
DELETE FROM MTH_ENTITY_SUST_HR_EMISSIONS;
DELETE FROM mth_entity_sustain_aspect_hour;
INSERT INTO mth_entity_sustain_aspect_hour
(esa_hr_pk_key, plant_fk_key, entity_fk_key, entity_type,
sustain_aspect_fk_key,
sustain_aspect, usage_category, meter_fk_key, meter_category,
from_time, to_time, hour_fk_key, shift_workday_fk_key,
entity_name, entity_type_name,
sustain_aspect_name, usage_category_name, meter_type,
meter_type_name, meter_category_name,
simulation_name, usage_value, usage_uom, usage_cost,
currency, creation_date, last_update_date)
SELECT max(esa_pk_key) AS esa_hr_pk_key,
a.plant_fk_key, a.entity_fk_key, a.entity_type,
a.sustain_aspect_fk_key,
a.sustain_aspect, a.usage_category, a.meter_fk_key,
a.meter_category, h.from_time, h.to_time, a.hour_fk_key,
a.shift_workday_fk_key, a.entity_name, a.entity_type_name,
a.sustain_aspect_name, a.usage_category_name, a.meter_type,
a.meter_type_name, a.meter_category_name,
a.simulation_name, Sum(a.usage_value) AS usage_value,
a.usage_uom, Sum(a.usage_cost) AS usage_cost,
a.currency, SYSDATE AS creation_date,
SYSDATE AS last_update_date
FROM mth_entity_sustain_aspect a, mth_hour_d h
WHERE a.hour_fk_key = h.hour_pk_key
GROUP BY a.plant_fk_key, a.entity_fk_key, a.entity_type,
a.sustain_aspect, a.sustain_aspect_fk_key, a.usage_category, a.meter_fk_key,
a.meter_category, h.from_time, h.to_time, a.hour_fk_key,
a.shift_workday_fk_key, a.entity_name, a.entity_type_name,
a.sustain_aspect_name, a.usage_category_name, a.meter_type,
a.meter_type_name, a.meter_category_name,
a.simulation_name, a.usage_uom, a.currency
order BY a.entity_fk_key, a.entity_type, a.sustain_aspect,
a.sustain_aspect_fk_key,
a.usage_category, a.meter_fk_key, a.meter_category,
h.from_time, h.to_time, a.hour_fk_key,a.shift_workday_fk_key,
a.entity_name, a.entity_type_name,
a.sustain_aspect_name, a.usage_category_name, a.meter_type,
a.meter_type_name, a.meter_category_name,
a.simulation_name, a.usage_uom, a.currency;
'Error occurs when inserting MTH_ENTITY_SUSTAIN_ASPECT_HOUR table:'
||SQLCODE||' -ERROR- '||SQLERRM);
INSERT INTO MTH_ENTITY_SUST_EMISSIONS
(esa_fk_key, emission_code, emission_name, emission_quantity,
emission_uom)
SELECT p_shift(i).esa_pk_key,
y.emission_code,
(SELECT meaning FROM fnd_lookup_values_vl
WHERE lookup_type = 'MTH_SUSTAIN_EMISSION'
AND lookup_code = y.emission_code) emission_name,
p_shift(i).usage_value *
y.average_emission_factor AS emission_quantity,
(SELECT emission_uom FROM mth_sustain_emissions
WHERE sustain_aspect_fk_key = w.sustain_aspect_pk_key
AND emission_code = y.emission_code) emission_uom
FROM MTH_METER_ENTITIES x, mth_meters x1,
(SELECT m.plant_fk_key, m.effective_date, m.expiration_date, m.sustain_aspect_fk_key,
m.average_planned_cost, n.emission_code, Nvl(n.average_emission_factor,0) average_emission_factor
FROM MTH_SITE_SUSTAINABILITIES m,
(SELECT a.site_sustain_pk_key, c.sustain_emission_fk_key, d.emission_code,
sum(b.planned_usage_percentage/100 * Nvl(c.emission_factor,0)) average_emission_factor
FROM MTH_SITE_SUSTAINABILITIES a, MTH_SITE_SUSTAIN_SOURCES b,
MTH_SOURCE_EMISSION_FACTORS c, MTH_SUSTAIN_EMISSIONS d
WHERE a.site_sustain_pk_key = b.site_sustain_fk_key
AND b.site_sustain_source_pk_key = c.site_sustain_source_fk_key
AND d.sustain_emission_pk_key = c.sustain_emission_fk_key
AND a.sustain_aspect_fk_key = d.sustain_aspect_fk_key
AND d.status = 'ACTIVE'
AND p_shift(i).to_time BETWEEN a.effective_date AND Nvl(a.expiration_date, SYSDATE)
GROUP by a.site_sustain_pk_key, c.sustain_emission_fk_key, d.emission_code
) n
WHERE m.site_sustain_pk_key = n.site_sustain_pk_key
AND p_shift(i).to_time BETWEEN m.effective_date AND Nvl(m.expiration_date, SYSDATE)
) y,
(SELECT f.*, g.meaning entity_type_name
FROM mth_all_entities_v f, fnd_lookup_values_vl g
WHERE Upper(f.entity_type) = g.lookup_code
AND (g.lookup_type = 'MTH_USER_DEFINED_ENTITIES' OR
g.lookup_type = 'MTH_OTHER_ENTITY_TYPE')
) z,
(SELECT f.sustain_aspect_pk_key, f.sustain_aspect_code,
f.usage_uom, g.meaning
FROM mth_sustain_aspects f, fnd_lookup_values_vl g
WHERE f.sustain_aspect_code = g.lookup_code
AND g.lookup_type = 'MTH_SUSTAIN_ASPECT'
) w
WHERE x.STATUS = 'ACTIVE'
AND x.meter_fk_key = x1.meter_pk_key
AND x.entity_fk_key = z.entity_pk_key
AND x.entity_type = Upper(z.entity_type)
AND y.plant_fk_key = z.site_id
AND w.sustain_aspect_pk_key = y.sustain_aspect_fk_key
AND w.sustain_aspect_pk_key = x1.sustain_aspect_fk_key
AND x.meter_fk_key = p_shift(i).meter_fk_key
AND x.entity_fk_key = p_shift(i).entity_fk_key;
'Error occurs when inserting data into MTH_ENTITY_SUST_EMISSIONS table:'
|| SQLCODE||' -ERROR- '||SQLERRM);
SELECT MTH_ENTITY_SUSTAIN_ASPECT_S.NEXTVAL
INTO p_shift(i).esa_pk_key
FROM dual;
INSERT INTO mth_entity_sustain_aspect
(esa_pk_key, plant_fk_key, entity_fk_key, entity_type,
sustain_aspect_fk_key,
sustain_aspect, usage_category, meter_fk_key, meter_category,
from_time, to_time, hour_fk_key, shift_workday_fk_key,
entity_name, entity_type_name,
sustain_aspect_name, usage_category_name, meter_type,
meter_type_name, meter_category_name,
simulation_name, usage_value, usage_uom, usage_cost,
currency, creation_date, last_update_date)
SELECT p_shift(i).esa_pk_key, y.plant_fk_key, x.entity_fk_key,
z.entity_type, w.sustain_aspect_pk_key, w.sustain_aspect_code,
Nvl(x.usage_category_code, -99999), x.meter_fk_key,
Nvl(x.meter_category_code, -99999),
p_shift(i).from_time, p_shift(i).to_time,
p_shift(i).hour_fk_key, p_shift(i).shift_workday_fk_key,
z.entity_name, z.entity_type_name,
w.meaning, (SELECT meaning FROM fnd_lookup_values_vl
WHERE lookup_type = 'MTH_ENERGY_USAGE_CATEGORIES'
AND lookup_code = x.usage_category_code) usage_category_name,
x1.meter_type, (SELECT meaning FROM fnd_lookup_values_vl
WHERE lookup_type = 'MTH_METER_TYPE'
AND lookup_code = x1.meter_type) meter_type_name,
(SELECT meaning FROM fnd_lookup_values_vl
WHERE lookup_type = 'MTH_METER_CATEGORY'
AND lookup_code = x.meter_category_code) meter_category_name,
(SELECT meaning FROM fnd_lookup_values_vl
WHERE lookup_type = 'MTH_SIMULATION_NAME'
AND lookup_code = x.simulation_name_code) simulation_name,
p_shift(i).usage_value,
w.usage_uom,
p_shift(i).usage_value * y.average_planned_cost,
(SELECT i.currency_code FROM mth_plants_d i
WHERE i.plant_pk_key = z.site_id) currency,
SYSDATE, SYSDATE
FROM MTH_METER_ENTITIES x, mth_meters x1,
(SELECT m.plant_fk_key, m.effective_date, m.expiration_date, m.sustain_aspect_fk_key,
m.average_planned_cost
FROM MTH_SITE_SUSTAINABILITIES m
WHERE p_shift(i).to_time BETWEEN m.effective_date AND Nvl(m.expiration_date, SYSDATE)
) y,
(SELECT f.*, g.meaning entity_type_name
FROM mth_all_entities_v f, fnd_lookup_values_vl g
WHERE Upper(f.entity_type) = g.lookup_code
AND (g.lookup_type = 'MTH_USER_DEFINED_ENTITIES' OR
g.lookup_type = 'MTH_OTHER_ENTITY_TYPE')
) z,
(SELECT f.sustain_aspect_pk_key, f.sustain_aspect_code,
f.usage_uom, g.meaning
FROM mth_sustain_aspects f, fnd_lookup_values_vl g
WHERE f.sustain_aspect_code = g.lookup_code
AND g.lookup_type = 'MTH_SUSTAIN_ASPECT'
) w
WHERE x.STATUS = 'ACTIVE'
AND x.meter_fk_key = x1.meter_pk_key
AND x.entity_fk_key = z.entity_pk_key
AND x.entity_type = Upper(z.entity_type)
AND y.plant_fk_key = z.site_id
AND w.sustain_aspect_pk_key = y.sustain_aspect_fk_key
AND w.sustain_aspect_pk_key = x1.sustain_aspect_fk_key
AND x.meter_fk_key = p_shift(i).meter_fk_key
AND x.entity_fk_key = p_shift(i).entity_fk_key;
'Error occurs when inserting MTH_ENTITY_SUSTAIN_ASPECT table:'
||SQLCODE||' -ERROR- '||SQLERRM);