SET ECHO ON
SPOOL XCViews.OUT

undefine tbl_owner
undefine tbl_hdb 
define tbl_owner = &1
define tbl_hdb = &2



DROP VIEW &&tbl_owner..XC_SITES;

CREATE VIEW &&tbl_owner..XC_SITES
AS
SELECT UNIQUE
	UPPER(A.STATION_ID) STATION_ID,
	A.ENABLED,
	A.UNIT_ID,
	B.description SITE_COMMENT, 
	A.INSTALLATION_DATE,
	A.AGENCY,
	A.COUNTRY,
	A.DISTRICT,
	A.CITY,
	C.STATE_CODE STATE,
	A.COUNTY,
	NULL BASIN,               -- !!change this to by the correct basin ID!! --
	NULL LATITUDE,
	NULL LONGITUDE,
	B.elevation ELEVATION,
    A.COEFFICIENT1,
    A.COEFFICIENT2,
    A.COEFFICIENT3,
	A.WEIGHTING_FACTOR, 
	A.SATELLITE_ID,
	A.PRIMARY_CHANNEL,
	A.RANDOM_CHANNEL,
	A.REPORTING_TIME,
	A.REPORTING_INTERVAL,
	A.LAST_UPDATE,
	A.TZONE_CODE,
	A.GMT_OFFSET,
	B.shef_code SHEF_ID,
	A.ALTERNATE_CHAR_ID_1,
	A.ALTERNATE_CHAR_ID_2, 
	A.GPSTRING1,
	A.GPSTRING2,
	A.GPNUMBER1,
	A.GPNUMBER2,
	A.SETUP_FILE_NAME,
	A.PICTURE_FILE_NAME
FROM &&tbl_owner..XC_EXT_SITES A, &&tbl_hdb..hdb_site B, &&tbl_hdb..hdb_state C 
WHERE UPPER(A.STATION_ID) = UPPER(B.SITE_COMMON_NAME(+)) AND 
    B.state_id = C.STATE_ID(+);


DROP VIEW &&tbl_owner..XC_SITESENSORS;

CREATE VIEW &&tbl_owner..XC_SITESENSORS
AS
SELECT
 A.STE_STATION_ID             ,
 A.SENSORNAME                 ,
 A.ENABLED                    ,
 A.DATA_TABLE                 ,
 A.DESCRIPTION                ,
 A.MANUFACTURER               ,
 A.MODEL_NUMBER               ,
 A.SHEF_PE_CODE               ,
 A.PARAMETER_CODE             ,
 A.ALT_PARAMETER_CODE_1       ,
 A.ALT_PARAMETER_CODE_2       ,
 A.EQUATION                   ,
 A.RATING_TABLE               ,
 A.OFFSET                     ,
 A.SHIFT                      ,
 A.RIGHT_DIGIT                ,
 A.HIGH_HIGH_LIMIT            ,
 A.HIGH_HIGH_ALARM            ,
 A.HIGH_LIMIT                 ,
 A.HIGH_ALARM                 ,
 A.LOW_LIMIT                  ,
 A.LOW_ALARM                  ,
 A.LOW_LOW_LIMIT              ,
 A.LOW_LOW_ALARM              ,
 A.ROC_LIMIT                  ,
 A.ROC_ALARM                  ,
 A.ROC_INTERVAL               ,
 A.DEADBAND                   ,
 A.NO_CHANGE_INTERVAL         ,
 A.NO_CHANGE_ALARM            ,
 A.TEXT_LIMIT                 ,
 A.TEXT_ALARM                 ,
 A.COEFFICIENT1               ,  
 A.COEFFICIENT2               ,
 A.COEFFICIENT3               ,
 A.COEFFICIENT4               ,
 A.COEFFICIENT5               ,
 A.GPNUMBER1                  ,
 A.GPNUMBER2                  ,
 A.GPSTRING1                  ,
 A.GPSTRING2                  ,
 A.ALERT_ID                   ,
 A.ALERT_TYPE                 ,  
 A.site_datatype_id           ,     -- HDB special
 A.interval                         -- HDB special
FROM XC_EXT_SITESENSORS A;


DROP VIEW &&tbl_owner..XC_DATA1;

CREATE VIEW &&tbl_owner..XC_DATA1
AS
SELECT
    --B.site_datatype_id          ,
    C.STE_STATION_ID STATION_ID ,
    C.SENSORNAME                ,
    B.START_DATE_TIME TIME_TAG  ,
    A.SOURCE                    ,
    A.ORIG_VALUE                ,
    B.value ED_VALUE            ,
    A.FLAG1                     ,
    A.FLAG2                     ,
    A.FLAG3                     ,
    A.FLAG4                     ,
    A.HIGH_HIGH_FLAG            ,
    A.HIGH_FLAG                 ,
    A.LOW_FLAG                  ,
    A.LOW_LOW_FLAG              ,
    A.ROC_FLAG                  ,
    A.NO_CHG_FLAG               ,
    A.ALARM_FLAG 
FROM &&tbl_owner..XC_EXT_DATA1 A, &&tbl_hdb..r_base B, &&tbl_owner..XC_EXT_SITESENSORS C
WHERE
    C.SITE_DATATYPE_ID = A.SITE_DATATYPE_ID AND
	C.INTERVAL = A.INTERVAL AND
	A.SITE_DATATYPE_ID = B.SITE_DATATYPE_ID AND 
	A.INTERVAL = B.INTERVAL AND 
	A.START_DATE_TIME = B.START_DATE_TIME AND 
	A.END_DATE_TIME = B.END_DATE_TIME;

SPOOL OFF
