CREATE OR REPLACE PROCEDURE DROP_OBJECT(ObjName varchar2, ObjType varchar2)
IS
v_counter number := 0;
begin
if ObjType = ‘TABLE’ then
select count(*) into v_counter from user_tables where table_name = upper(ObjName);
if v_counter > 0 then
execute immediate ‘drop table ‘ || ObjName || ‘ cascade constraints’;
end if;
end if;
if ObjType = ‘PROCEDURE’ then
select count(*) into v_counter from User_Objects where object_type = ‘PROCEDURE’ and OBJECT_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate ‘DROP PROCEDURE ‘ || ObjName;
end if;
end if;
if ObjType = ‘FUNCTION’ then
select count(*) into v_counter from User_Objects where object_type = ‘FUNCTION’ and OBJECT_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate ‘DROP FUNCTION ‘ || ObjName;
end if;
end if;
if ObjType = ‘TRIGGER’ then
select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate ‘DROP TRIGGER ‘ || ObjName;
end if;
end if;
if ObjType = ‘VIEW’ then
select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate ‘DROP VIEW ‘ || ObjName;
end if;
end if;
if ObjType = ‘SEQUENCE’ then
select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
if v_counter > 0 then
execute immediate ‘DROP SEQUENCE ‘ || ObjName;
end if;
end if;
end;
CREATE OR REPLACE FUNCTION INDEX_EXISTS (ObjName varchar2)
RETURN BOOLEAN
IS
v_counter number := 0;
BEGIN
select count(*) into v_counter from User_Objects WHERE OBJECT_NAME = ObjName;
if v_counter > 0 then
RETURN(true);
end if;
RETURN(false);
END;
/
IF (INDEX_EXISTS(‘TR_READINGPARAMS_PK’)) THEN
ALTER TABLE TR_READINGPARAMS DROP PRIMARY KEY CASCADE;
END IF
DROP_OBJECT(‘TR_READINGPARAMS’, ‘TABLE’);
CREATE TABLE TR_READINGPARAMS
(
TRR_REACODE VARCHAR2(30 BYTE),
TRR_BIRIMFIYAT NUMBER(24,6) NOT NULL,
TRR_BIRIMCARPAN NUMBER(24,6)
)
TABLESPACE DATA_SIEMENSTEST
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX TR_READINGPARAMS_PK ON TR_READINGPARAMS
(TRR_REACODE)
LOGGING
TABLESPACE DATA_SIEMENSTEST
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE TR_READINGPARAMS ADD (
CONSTRAINT TR_READINGPARAMS_PK
PRIMARY KEY
(TRR_REACODE)
USING INDEX
TABLESPACE DATA_SIEMENSTEST
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));
DROP_OBJECT(‘TR_OKUMAPARAM’, ‘TRIGGER’);
CREATE OR REPLACE TRIGGER tr_okumaparam
AFTER INSERT
ON R5READINGS REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpvar NUMBER;
metclass VARCHAR2 (8);
birimfiyat NUMBER (24, 6);
birimcarpan NUMBER (24, 6);
BEGIN
SELECT met_class
INTO metclass
FROM r5meters
WHERE met_code = :NEW.rea_meter;
IF metclass = ‘ELK’
THEN
SELECT elektrikbirimfiyat, elektrikcarpan
INTO birimfiyat, birimcarpan
FROM tr_objectvals
WHERE obj_code = :NEW.rea_object;
ELSIF metclass = ‘SU’
THEN
SELECT subirimfiyat
INTO birimfiyat
FROM tr_objectvals
WHERE obj_code = :NEW.rea_object;
ELSIF metclass = ‘GAZ’
THEN
SELECT dgbirimfiyat, dgbirimcarpan
INTO birimfiyat, birimcarpan
FROM tr_objectvals
WHERE obj_code = :NEW.rea_object;
END IF;
INSERT INTO tr_readingparams
VALUES (:NEW.rea_code, birimfiyat, birimcarpan);
EXCEPTION
WHEN OTHERS
THEN
— Consider logging the error and then re-raise
RAISE;
END tr_okumaparam;
/
DROP_OBJECT(‘TR_KULLANICISAYACTIPI’, ‘VIEW’);
/* Formatted on 30.07.2012 11:07:27 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW TR_KULLANICISAYACTIPI
(
USERCODE,
METERCLASS
)
AS
SELECT DISTINCT prv_code, met_class
FROM r5meters, r5objusagedefs, r5propertyvalues
WHERE met_code = oud_meter
AND oud_object = prv_value
AND prv_property = ‘MAGAZA’
AND prv_rentity = ‘USER’
ORDER BY prv_code ASC;
DROP_OBJECT(‘TR_OBJECTS’, ‘VIEW’);
/* Formatted on 30.07.2012 11:26:19 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW TR_OBJECTS
(
OBJ_OBTYPE,
OBJ_OBRTYPE,
OBJ_CODE,
OBJ_DESC,
OBJ_CLASS,
OBJ_CATEGORY,
OBJ_COSTCODE,
OBJ_LTYPE,
OBJ_LOCATION,
OBJ_TAG,
OBJ_PTYPE,
OBJ_POSITION,
OBJ_PARENT,
OBJ_DEPEND,
OBJ_MANUFACT,
OBJ_MRC,
OBJ_PART,
OBJ_SERIALNO,
OBJ_STORE,
OBJ_STATUS,
OBJ_RSTATUS,
OBJ_COMMISS,
OBJ_WITHDRAW,
OBJ_RECORD,
OBJ_GROUP,
OBJ_USER,
OBJ_ORDER,
OBJ_ORDERLINE,
OBJ_PRODUCTION,
OBJ_PRIMARYUOM,
OBJ_ACD,
OBJ_NOTUSED,
OBJ_SAFETY,
OBJ_CRITICALITY,
OBJ_PROFILE,
OBJ_MANUFACTMODEL,
OBJ_MANUFACTREVNUM,
OBJ_VALUE,
OBJ_SOURCESYSTEM,
OBJ_SOURCECODE,
OBJ_VARIABLE1,
OBJ_VARIABLE2,
OBJ_VARIABLE3,
OBJ_VARIABLE4,
OBJ_VARIABLE5,
OBJ_VARIABLE6,
OBJ_STATE,
OBJ_RSTATE,
OBJ_CN,
OBJ_ORG,
OBJ_CLASS_ORG,
OBJ_LOCATION_ORG,
OBJ_POSITION_ORG,
OBJ_PARENT_ORG,
OBJ_ORDER_ORG,
OBJ_PART_ORG,
OBJ_BIN,
OBJ_LOT,
OBJ_SYSLEVEL,
OBJ_ASMLEVEL,
OBJ_COMPLEVEL,
OBJ_UPDATED,
OBJ_UPDATEDBY,
OBJ_PREVENTEVTCOMP,
OBJ_PERSON,
OBJ_ESIGNATURE,
OBJ_UPDATECOUNT,
OBJ_DORMSTART,
OBJ_DORMEND,
OBJ_DORMREUSE,
OBJ_GISOBJID,
OBJ_GISPROFILE,
OBJ_PROFILE_ORG,
OBJ_SOLDDATE,
OBJ_TRANSFERDATE,
OBJ_CGMP,
OBJ_TESTPOINTUOM,
OBJ_OUTPUTUOM,
OBJ_INCREMENT,
OBJ_PRECISION,
OBJ_PIDNO,
OBJ_PIDDRAWING,
OBJ_SERVICEPERC,
OBJ_DEVICETOLFROM,
OBJ_DEVICETOLTO,
OBJ_DEVICETOLTYPE,
OBJ_DEVICERANGEFROM,
OBJ_DEVICERANGETO,
OBJ_OUTPUTRANGEFROM,
OBJ_OUTPUTRANGETO,
OBJ_PROCESSTOLFROM,
OBJ_PROCESSTOLTO,
OBJ_PROCESSRANGEFROM,
OBJ_PROCESSRANGETO,
OBJ_SET,
OBJ_SOP,
OBJ_LOOP,
OBJ_INSTRUMENT,
OBJ_INSTRUMENTTYPE,
OBJ_INTERFACE,
OBJ_LINKGISWO,
OBJ_GISLAYER,
OBJ_XLOCATION,
OBJ_YLOCATION,
OBJ_LENGTH,
OBJ_LENGTHUOM,
OBJ_LINEARREFUOM,
OBJ_LINEARREFPRECISION,
OBJ_GEOREF,
OBJ_VEHICLE,
OBJ_FLEETCUSTOMER,
OBJ_FLEETCUSTOMER_ORG,
OBJ_BILLINGCODE,
OBJ_BILLINGCODE_ORG,
OBJ_MARKUPCODE,
OBJ_MARKUPCODE_ORG,
OBJ_VEHICLERSTATUS,
OBJ_VEHICLESTATUS,
OBJ_ISSUEDTO,
OBJ_JECATEGORY,
OBJ_JESOURCE,
OBJ_GLTRANSFERFLAG,
OBJ_GLTRANSFER,
OBJ_MINPENALTY,
OBJ_PENALTYFACTOR,
OBJ_CALGROUP,
OBJ_CALGROUPORG,
OBJ_UDFCHAR01,
OBJ_UDFCHAR02,
OBJ_UDFCHAR03,
OBJ_UDFCHAR04,
OBJ_UDFCHAR05,
OBJ_UDFCHAR06,
OBJ_UDFCHAR07,
OBJ_UDFCHAR08,
OBJ_UDFCHAR09,
OBJ_UDFCHAR10,
OBJ_UDFCHAR11,
OBJ_UDFCHAR12,
OBJ_UDFCHAR13,
OBJ_UDFCHAR14,
OBJ_UDFCHAR15,
OBJ_UDFCHAR16,
OBJ_UDFCHAR17,
OBJ_UDFCHAR18,
OBJ_UDFCHAR19,
OBJ_UDFCHAR20,
OBJ_UDFCHAR21,
OBJ_UDFCHAR22,
OBJ_UDFCHAR23,
OBJ_UDFCHAR24,
OBJ_UDFCHAR25,
OBJ_UDFCHAR26,
OBJ_UDFCHAR27,
OBJ_UDFCHAR28,
OBJ_UDFCHAR29,
OBJ_UDFCHAR30,
OBJ_UDFNUM01,
OBJ_UDFNUM02,
OBJ_UDFNUM03,
OBJ_UDFNUM04,
OBJ_UDFNUM05,
OBJ_UDFDATE01,
OBJ_UDFDATE02,
OBJ_UDFDATE03,
OBJ_UDFDATE04,
OBJ_UDFDATE05,
OBJ_UDFCHKBOX01,
OBJ_UDFCHKBOX02,
OBJ_UDFCHKBOX03,
OBJ_UDFCHKBOX04,
OBJ_UDFCHKBOX05,
OBJ_SDMPRESENT,
OBJ_SDMPATH,
OBJ_COSTOFNEEDEDREPAIRS,
OBJ_REPLACEMENTVALUE,
OBJ_FACILITYCONDITIONINDEX,
OBJ_BILLABLE,
OBJ_GASINDEX,
OBJ_FLOORAREA,
OBJ_FLOORAREAUOM,
OBJ_ESTREVENUE,
OBJ_REGION,
OBJ_PRIMARYUSE,
OBJ_YEARBUILT,
OBJ_SERVICELIFE,
OBJ_RESOURCE,
OBJ_RESOURCEPRESENT,
OBJ_LASTSTATUSUPDATE
)
AS
SELECT OBJ_OBTYPE,
OBJ_OBRTYPE,
OBJ_CODE,
OBJ_DESC,
OBJ_CLASS,
OBJ_CATEGORY,
OBJ_COSTCODE,
OBJ_LTYPE,
OBJ_LOCATION,
OBJ_TAG,
OBJ_PTYPE,
OBJ_POSITION,
OBJ_PARENT,
OBJ_DEPEND,
OBJ_MANUFACT,
OBJ_MRC,
OBJ_PART,
OBJ_SERIALNO,
OBJ_STORE,
OBJ_STATUS,
OBJ_RSTATUS,
OBJ_COMMISS,
OBJ_WITHDRAW,
OBJ_RECORD,
OBJ_GROUP,
OBJ_USER,
OBJ_ORDER,
OBJ_ORDERLINE,
OBJ_PRODUCTION,
OBJ_PRIMARYUOM,
OBJ_ACD,
OBJ_NOTUSED,
OBJ_SAFETY,
OBJ_CRITICALITY,
OBJ_PROFILE,
OBJ_MANUFACTMODEL,
OBJ_MANUFACTREVNUM,
OBJ_VALUE,
OBJ_SOURCESYSTEM,
OBJ_SOURCECODE,
(SELECT CST_DESC
FROM R5COSTCODES
WHERE CST_CODE = OBJ_COSTCODE AND CST_ORG = OBJ_ORG)
OBJ_VARIABLE1,
OBJ_VARIABLE2,
OBJ_VARIABLE3,
OBJ_VARIABLE4,
OBJ_VARIABLE5,
OBJ_VARIABLE6,
OBJ_STATE,
OBJ_RSTATE,
OBJ_CN,
OBJ_ORG,
OBJ_CLASS_ORG,
OBJ_LOCATION_ORG,
OBJ_POSITION_ORG,
OBJ_PARENT_ORG,
OBJ_ORDER_ORG,
OBJ_PART_ORG,
OBJ_BIN,
OBJ_LOT,
OBJ_SYSLEVEL,
OBJ_ASMLEVEL,
OBJ_COMPLEVEL,
OBJ_UPDATED,
OBJ_UPDATEDBY,
OBJ_PREVENTEVTCOMP,
OBJ_PERSON,
OBJ_ESIGNATURE,
OBJ_UPDATECOUNT,
OBJ_DORMSTART,
OBJ_DORMEND,
OBJ_DORMREUSE,
OBJ_GISOBJID,
OBJ_GISPROFILE,
OBJ_PROFILE_ORG,
OBJ_SOLDDATE,
OBJ_TRANSFERDATE,
OBJ_CGMP,
OBJ_TESTPOINTUOM,
OBJ_OUTPUTUOM,
OBJ_INCREMENT,
OBJ_PRECISION,
OBJ_PIDNO,
OBJ_PIDDRAWING,
OBJ_SERVICEPERC,
OBJ_DEVICETOLFROM,
OBJ_DEVICETOLTO,
OBJ_DEVICETOLTYPE,
OBJ_DEVICERANGEFROM,
OBJ_DEVICERANGETO,
OBJ_OUTPUTRANGEFROM,
OBJ_OUTPUTRANGETO,
OBJ_PROCESSTOLFROM,
OBJ_PROCESSTOLTO,
OBJ_PROCESSRANGEFROM,
OBJ_PROCESSRANGETO,
OBJ_SET,
OBJ_SOP,
OBJ_LOOP,
OBJ_INSTRUMENT,
OBJ_INSTRUMENTTYPE,
OBJ_INTERFACE,
OBJ_LINKGISWO,
OBJ_GISLAYER,
OBJ_XLOCATION,
OBJ_YLOCATION,
OBJ_LENGTH,
OBJ_LENGTHUOM,
OBJ_LINEARREFUOM,
OBJ_LINEARREFPRECISION,
OBJ_GEOREF,
OBJ_VEHICLE,
OBJ_FLEETCUSTOMER,
OBJ_FLEETCUSTOMER_ORG,
OBJ_BILLINGCODE,
OBJ_BILLINGCODE_ORG,
OBJ_MARKUPCODE,
OBJ_MARKUPCODE_ORG,
OBJ_VEHICLERSTATUS,
OBJ_VEHICLESTATUS,
OBJ_ISSUEDTO,
OBJ_JECATEGORY,
OBJ_JESOURCE,
OBJ_GLTRANSFERFLAG,
OBJ_GLTRANSFER,
OBJ_MINPENALTY,
OBJ_PENALTYFACTOR,
OBJ_CALGROUP,
OBJ_CALGROUPORG,
OBJ_UDFCHAR01,
OBJ_UDFCHAR02,
OBJ_UDFCHAR03,
OBJ_UDFCHAR04,
OBJ_UDFCHAR05,
OBJ_UDFCHAR06,
OBJ_UDFCHAR07,
OBJ_UDFCHAR08,
OBJ_UDFCHAR09,
OBJ_UDFCHAR10,
OBJ_UDFCHAR11,
OBJ_UDFCHAR12,
OBJ_UDFCHAR13,
OBJ_UDFCHAR14,
OBJ_UDFCHAR15,
OBJ_UDFCHAR16,
OBJ_UDFCHAR17,
OBJ_UDFCHAR18,
OBJ_UDFCHAR19,
OBJ_UDFCHAR20,
OBJ_UDFCHAR21,
OBJ_UDFCHAR22,
OBJ_UDFCHAR23,
OBJ_UDFCHAR24,
OBJ_UDFCHAR25,
OBJ_UDFCHAR26,
OBJ_UDFCHAR27,
OBJ_UDFCHAR28,
OBJ_UDFCHAR29,
OBJ_UDFCHAR30,
OBJ_UDFNUM01,
OBJ_UDFNUM02,
OBJ_UDFNUM03,
OBJ_UDFNUM04,
OBJ_UDFNUM05,
OBJ_UDFDATE01,
OBJ_UDFDATE02,
OBJ_UDFDATE03,
OBJ_UDFDATE04,
OBJ_UDFDATE05,
OBJ_UDFCHKBOX01,
OBJ_UDFCHKBOX02,
OBJ_UDFCHKBOX03,
OBJ_UDFCHKBOX04,
OBJ_UDFCHKBOX05,
OBJ_SDMPRESENT,
OBJ_SDMPATH,
OBJ_COSTOFNEEDEDREPAIRS,
OBJ_REPLACEMENTVALUE,
OBJ_FACILITYCONDITIONINDEX,
OBJ_BILLABLE,
OBJ_GASINDEX,
OBJ_FLOORAREA,
OBJ_FLOORAREAUOM,
OBJ_ESTREVENUE,
OBJ_REGION,
OBJ_PRIMARYUSE,
OBJ_YEARBUILT,
OBJ_SERVICELIFE,
OBJ_RESOURCE,
OBJ_RESOURCEPRESENT,
OBJ_LASTSTATUSUPDATE
FROM R5OBJECTS;
DROP_OBJECT(‘TR_OBJECTVALS’, ‘VIEW’);
/* Formatted on 30.07.2012 11:26:37 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW TR_OBJECTVALS
(
OBJ_CODE,
ELEKTRIKBIRIMFIYAT,
ELEKTRIKCARPAN,
SUBIRIMFIYAT,
DGBIRIMFIYAT,
DGBIRIMCARPAN
)
AS
SELECT obj_code,
obj_udfnum01 elektrikbirimfiyat,
obj_udfnum02 elektrikcarpan,
obj_udfnum03 subirimfiyat,
obj_udfnum04 dgbirimfiyat,
obj_udfnum05 dgbirimcarpan
FROM r5objects;
DROP_OBJECT(‘TR_OTURUMMAGAZA’, ‘VIEW’);
/* Formatted on 30.07.2012 11:26:47 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW TR_OTURUMMAGAZA
(
SESSID,
USERCODE,
STORECODE,
STOREORG,
STOREDESC,
OBJ_UDFCHAR01,
OBJ_UDFCHAR02,
OBJ_UDFCHAR03,
OBJ_UDFCHAR04
)
AS
SELECT ses_sessionid sessid,
ses_user usercode,
obj_code storecode,
obj_org storeorg,
obj_desc storedesc,
OBJ_UDFCHAR01,
OBJ_UDFCHAR02,
OBJ_UDFCHAR03,
OBJ_UDFCHAR04
FROM r5objects, r5propertyvalues, r5sessions
WHERE obj_code = prv_value
AND prv_property = ‘MAGAZA’
AND prv_rentity = ‘USER’
AND ses_user = prv_code
AND ses_user <> ‘DATABRIDGEINTERNALUSER’;
DROP_OBJECT(‘TR_SAYACKULLANICIALAN’, ‘VIEW’);
/* Formatted on 30.07.2012 11:27:02 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW 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;
DROP_OBJECT(‘TR_SAYACOKUMADEGER’, ‘VIEW’);
/* Formatted on 30.07.2012 11:27:24 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FORCE VIEW TR_SAYACOKUMADEGER
(
REA_CODE,
REA_METER,
REA_DATE,
REA_READING,
REA_CALCUOM,
REA_OBJECT,
REA_DIFF,
REA_CLASS,
BIRIMFIYAT,
BIRIMCARPAN
)
AS
SELECT rea_code,
rea_meter,
rea_date,
rea_reading,
rea_calcuom,
rea_object,
rea_diff,
met_class rea_class,
trr_birimfiyat,
trr_birimcarpan
FROM r5readings,
r5objusagedefs,
r5meters,
tr_readingparams
WHERE rea_object = oud_object
AND oud_meter = met_code
AND rea_meter = oud_meter
AND trr_reacode = rea_code;
İş'te Zekâm 
Cevapla