DROP VIEW SIEMENSTEST TR SAYACKULLANICIALAN * Formatted on…

DROP VIEW SIEMENSTEST.TR_SAYACKULLANICIALAN;

/* Formatted on 02.08.2012 14:06:38 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW SIEMENSTEST.TR_SAYACKULLANICIALAN
(
PRV_CODE,
PRV_VALUE,
OUD_METER,
OUD_UOM,
UOM_DESC,
MET_CLASS,
LASTVAL
)
AS
SELECT prv_code,
prv_value,
oud_meter,
oud_uom,
uom_desc,
met_class,
tr_lastreadingval (prv_value, oud_meter) lastval
FROM r5objusagedefs,
r5propertyvalues,
r5meters,
r5uoms
WHERE oud_object = prv_value
AND oud_meter = met_code
AND prv_property = ‘MAGAZA’
AND prv_rentity = ‘USER’
AND oud_meter IS NOT NULL
AND uom_code = oud_uom
ORDER BY prv_code, oud_dfltdailyusg, met_class;

 
CREATE OR REPLACE FUNCTION SIEMENSTEST.tr_lastreadingval (
obj IN VARCHAR2,
meter IN VARCHAR2
)
RETURN DECIMAL
IS
tmpvar DECIMAL;
BEGIN
tmpvar := 0;

BEGIN
SELECT MAX (rea_reading)
INTO tmpvar
FROM (SELECT rea_reading
FROM r5readings
WHERE rea_object = obj AND rea_meter = meter
ORDER BY rea_date DESC);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;

RETURN tmpvar;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
— Consider logging the error and then re-raise
RAISE;
END tr_lastreadingval;
/