SET ECHO ON
SPOOL XCStoredProc.OUT


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

DROP PROCEDURE &&tbl_owner..XC_CREATE_SITE;
CREATE PROCEDURE &&tbl_owner..XC_CREATE_SITE ( 
        IN_STATION_ID IN VARCHAR2,
        IN_ENABLED IN VARCHAR2,
        IN_UNIT_ID IN VARCHAR2,
        IN_SITE_COMMENT IN VARCHAR2,
        IN_INSTALLATION_DATE IN DATE,
        IN_AGENCY IN VARCHAR2,
        IN_COUNTRY IN VARCHAR2,
        IN_DISTRICT IN VARCHAR2,
        IN_CITY IN VARCHAR2,
        IN_STATE IN VARCHAR2,
        IN_COUNTY IN VARCHAR2,
        IN_BASIN IN VARCHAR2,
        IN_LATITUDE IN FLOAT,
        IN_LONGITUDE IN FLOAT,
        IN_ELEVATION IN FLOAT,
        IN_COEFFICIENT1 IN FLOAT,
        IN_COEFFICIENT2 IN FLOAT,             
        IN_COEFFICIENT3 IN FLOAT,             
        IN_WEIGHTING_FACTOR IN FLOAT,
        IN_SATELLITE_ID IN VARCHAR2,
        IN_PRIMARY_CHANNEL IN NUMBER,
        IN_RANDOM_CHANNEL IN NUMBER,
        IN_REPORTING_TIME IN DATE,
        IN_REPORTING_INTERVAL IN FLOAT,
        IN_LAST_UPDATE IN DATE,
        IN_TZONE_CODE IN VARCHAR2,
        IN_GMT_OFFSET IN NUMBER,
        IN_SHEF_ID IN VARCHAR2,
        IN_ALTERNATE_CHAR_ID_1 IN VARCHAR2,
        IN_ALTERNATE_CHAR_ID_2 IN VARCHAR2,
        IN_GPSTRING1 IN VARCHAR2,
        IN_GPSTRING2 IN VARCHAR2,
        IN_GPNUMBER1 IN FLOAT,
        IN_GPNUMBER2 IN FLOAT,
        IN_SETUP_FILE_NAME IN VARCHAR2,
        IN_PICTURE_FILE_NAME IN VARCHAR2
        ) 
IS      
    v_rowcount NUMBER;
    v_stateid NUMBER(11);
    v_siteid number(11);
    v_dbsitecode varchar2(3);
    v_latitude varchar2(8);
    v_longitude varchar2(8);
    v_num NUMBER;
    v_basinid NUMBER;
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT count( * )
    INTO v_rowcount
    FROM &&tbl_hdb..HDB_SITE
    WHERE UPPER(SITE_COMMON_NAME) = UPPER(IN_STATION_ID);

    IF v_rowcount = 0 THEN

        -- comment the following statement to let XC_CREATE_SITE create new records in &&tbl_hdb..HDB_SITE
        &&tbl_hdb..DENY_ACTION ( 'XC_CREATE_SITE will not create new records in &&tbl_hdb..HDB_SITE.  The site name must exist in &&tbl_hdb..HDB_SITE.' );

        -- Try to insert new records into &&tbl_hdb..HDB_SITE
        v_siteid := &&tbl_hdb..populate_pk.get_pk_val('&&tbl_hdb..HDB_SITE', TRUE);

        IF v_siteid IS NULL THEN
          &&tbl_hdb..DENY_ACTION ( 'INVALID <NULL> SITE_ID' );
        END IF;

        IF IN_STATE IS NOT NULL THEN
            SELECT STATE_ID INTO v_stateid
            FROM &&tbl_hdb..HDB_STATE
            WHERE STATE_CODE = IN_STATE;
        ELSE
            v_stateid := NULL;
        END IF;

        IF IN_LATITUDE IS NOT NULL THEN
            v_num := IN_LATITUDE;
            SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
            INTO v_latitude 
            FROM dual;
        ELSE
            v_latitude := NULL;
        END IF;

        IF IN_LONGITUDE IS NOT NULL THEN
            v_num := IN_LONGITUDE;
            SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
            INTO v_longitude 
            FROM dual;
        ELSE
            v_longitude := NULL;
        END IF;

        v_dbsitecode := 'UC'; -- !!change this to get correct default value!! --

        v_basinid := NULL;      -- !!change this to get correct basin ID!! --

        INSERT INTO &&tbl_hdb..HDB_SITE (
            site_id                  , 
            site_name                ,  
            site_common_name         , 
            objecttype_id            , 
            parent_site_id           , 
            parent_objecttype_id     , 
            state_id                 , 
            basin_id                 , 
            lat                      , 
            longi                    , 
            hydrologic_unit          , 
            segment_no               , 
            river_mile               , 
            elevation                , 
            description              ,    
            nws_code                 ,   
            scs_id                   ,  
            shef_code                , 
            usgs_id                  , 
            db_site_code              
        )VALUES(
            v_siteid,                       --   site_id              
            UPPER(IN_STATION_ID),           --   site_name            
            UPPER(IN_STATION_ID),           --   site_common_name     
            9,                              --   objecttype_id        
            NULL,                           --   parent_site_id       
            NULL,                           --   parent_objecttype_id 
            v_stateid,                      --   state_id             
            v_basinid,                      --   basin_id             
            v_latitude,                     --   lat                  
            v_longitude,                    --   longi                
            NULL,                           --   hydrologic_unit      
            NULL,                           --   segment_no           
            NULL,                           --   river_mile           
            IN_ELEVATION,                   --   elevation            
            IN_SITE_COMMENT,                --   description          
            NULL,                           --   nws_code             
            NULL,                           --   scs_id               
            IN_SHEF_ID,                     --   shef_code            
            NULL,                           --   usgs_id              
            v_dbsitecode                    --   db_site_code         
        );

        INSERT INTO XC_EXT_SITES(
            STATION_ID              ,
            ENABLED                 ,
            UNIT_ID                 ,
            --SITE_COMMENT          ,
            INSTALLATION_DATE       ,
            AGENCY                  ,
            COUNTRY                 ,
            DISTRICT                ,
            CITY                    ,
            --IN_STATE              ,
            COUNTY                  ,
            --BASIN                 ,
            --LATITUDE              ,
            --LONGITUDE             ,
            --ELEVATION             ,
            COEFFICIENT1            ,
            COEFFICIENT2            ,
            COEFFICIENT3            ,
            WEIGHTING_FACTOR        ,
            SATELLITE_ID            ,
            PRIMARY_CHANNEL         ,
            RANDOM_CHANNEL          ,
            REPORTING_TIME          ,
            REPORTING_INTERVAL      ,
            LAST_UPDATE             ,
            TZONE_CODE              ,
            GMT_OFFSET              ,
            ALTERNATE_CHAR_ID_1     ,
            ALTERNATE_CHAR_ID_2     ,
            GPSTRING1               ,
            GPSTRING2               ,
            GPNUMBER1               ,
            GPNUMBER2               ,
            SETUP_FILE_NAME         ,
            PICTURE_FILE_NAME
        )VALUES (
            UPPER(IN_STATION_ID),
            UPPER(IN_ENABLED),
            UPPER(IN_UNIT_ID),
            --IN_SITE_COMMENT,
            IN_INSTALLATION_DATE,
            IN_AGENCY,
            IN_COUNTRY,
            IN_DISTRICT,
            IN_CITY,
            --IN_STATE,
            IN_COUNTY,
            --IN_BASIN,
            --IN_LATITUDE,
            --IN_LONGITUDE,
            --IN_ELEVATION,
            IN_COEFFICIENT1,
            IN_COEFFICIENT2,
            IN_COEFFICIENT3,
            IN_WEIGHTING_FACTOR,
            UPPER(IN_SATELLITE_ID),
            IN_PRIMARY_CHANNEL,
            IN_RANDOM_CHANNEL,
            IN_REPORTING_TIME,
            IN_REPORTING_INTERVAL,
            IN_LAST_UPDATE,
            IN_TZONE_CODE,
            IN_GMT_OFFSET,
            --IN_SHEF_ID,
            IN_ALTERNATE_CHAR_ID_1,
            IN_ALTERNATE_CHAR_ID_2,
            IN_GPSTRING1,
            IN_GPSTRING2,
            IN_GPNUMBER1,
            IN_GPNUMBER2,
            IN_SETUP_FILE_NAME,
            IN_PICTURE_FILE_NAME
        );
        COMMIT;
    ELSE 
        IF v_rowcount <> 1 THEN
            &&tbl_hdb..DENY_ACTION ( 'HDB_SITE.SITE_COMMON_NAME is not unique ' || IN_STATION_ID );
        END IF;
        INSERT INTO XC_EXT_SITES (
            STATION_ID              ,
            ENABLED                 ,
            UNIT_ID                 ,
            --SITE_COMMENT          ,
            INSTALLATION_DATE       ,
            AGENCY                  ,
            COUNTRY                 ,
            DISTRICT                ,
            CITY                    ,
            --STATE                 ,
            COUNTY                  ,
            --BASIN                 ,
            --LATITUDE              ,
            --LONGITUDE             ,
            --ELEVATION             ,
            COEFFICIENT1            ,
            COEFFICIENT2            ,
            COEFFICIENT3            ,
            WEIGHTING_FACTOR        ,
            SATELLITE_ID            ,
            PRIMARY_CHANNEL         ,
            RANDOM_CHANNEL          ,
            REPORTING_TIME          ,
            REPORTING_INTERVAL      ,
            LAST_UPDATE             ,
            TZONE_CODE              ,
            GMT_OFFSET              ,
            ALTERNATE_CHAR_ID_1     ,
            ALTERNATE_CHAR_ID_2     ,
            GPSTRING1               ,
            GPSTRING2               ,
            GPNUMBER1               ,
            GPNUMBER2               ,
            SETUP_FILE_NAME         ,
            PICTURE_FILE_NAME
        )VALUES (
            UPPER(IN_STATION_ID),
            UPPER(IN_ENABLED),
            UPPER(IN_UNIT_ID),
            --IN_SITE_COMMENT,
            IN_INSTALLATION_DATE,
            IN_AGENCY,
            IN_COUNTRY,
            IN_DISTRICT,
            IN_CITY,
            --IN_STATE,
            IN_COUNTY,
            --IN_BASIN,
            --IN_LATITUDE,
            --IN_LONGITUDE,
            --IN_ELEVATION,
            IN_COEFFICIENT1,
            IN_COEFFICIENT2,
            IN_COEFFICIENT3,
            IN_WEIGHTING_FACTOR,
            UPPER(IN_SATELLITE_ID),
            IN_PRIMARY_CHANNEL,
            IN_RANDOM_CHANNEL,
            IN_REPORTING_TIME,
            IN_REPORTING_INTERVAL,
            IN_LAST_UPDATE,
            IN_TZONE_CODE,
            IN_GMT_OFFSET,
            --IN_SHEF_ID,
            IN_ALTERNATE_CHAR_ID_1,
            IN_ALTERNATE_CHAR_ID_2,
            IN_GPSTRING1,
            IN_GPSTRING2,
            IN_GPNUMBER1,
            IN_GPNUMBER2,
            IN_SETUP_FILE_NAME,
            IN_PICTURE_FILE_NAME
        );
        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_CREATE_SITE;
CREATE PUBLIC SYNONYM XC_CREATE_SITE for &&tbl_owner..XC_CREATE_SITE;
GRANT EXECUTE ON &&tbl_owner..XC_CREATE_SITE to XCMANAGERS;

DROP PROCEDURE &&tbl_owner..XC_UPDATE_SITE;
CREATE PROCEDURE &&tbl_owner..XC_UPDATE_SITE ( 
        IN_STATION_ID IN VARCHAR2,
        IN_ENABLED IN VARCHAR2,
        IN_UNIT_ID IN VARCHAR2,
        IN_SITE_COMMENT IN VARCHAR2,
        IN_INSTALLATION_DATE IN DATE,
        IN_AGENCY IN VARCHAR2,
        IN_COUNTRY IN VARCHAR2,
        IN_DISTRICT IN VARCHAR2,
        IN_CITY IN VARCHAR2,
        IN_STATE IN VARCHAR2,
        IN_COUNTY IN VARCHAR2,
        IN_BASIN IN VARCHAR2,
        IN_LATITUDE IN FLOAT,
        IN_LONGITUDE IN FLOAT,
        IN_ELEVATION IN FLOAT,
        IN_COEFFICIENT1 IN FLOAT,
        IN_COEFFICIENT2 IN FLOAT,             
        IN_COEFFICIENT3 IN FLOAT,             
        IN_WEIGHTING_FACTOR IN FLOAT,
        IN_SATELLITE_ID IN VARCHAR2,
        IN_PRIMARY_CHANNEL IN NUMBER,
        IN_RANDOM_CHANNEL IN NUMBER,
        IN_REPORTING_TIME IN DATE,
        IN_REPORTING_INTERVAL IN FLOAT,
        IN_LAST_UPDATE IN DATE,
        IN_TZONE_CODE IN VARCHAR2,
        IN_GMT_OFFSET IN NUMBER,
        IN_SHEF_ID IN VARCHAR2,
        IN_ALTERNATE_CHAR_ID_1 IN VARCHAR2,
        IN_ALTERNATE_CHAR_ID_2 IN VARCHAR2,
        IN_GPSTRING1 IN VARCHAR2,
        IN_GPSTRING2 IN VARCHAR2,
        IN_GPNUMBER1 IN FLOAT,
        IN_GPNUMBER2 IN FLOAT,
        IN_SETUP_FILE_NAME IN VARCHAR2,
        IN_PICTURE_FILE_NAME IN VARCHAR2
        ) 
IS      
    v_rowcount NUMBER;
    v_stateid NUMBER(11);
    v_latitude varchar2(8);
    v_longitude varchar2(8);
    v_num NUMBER;
    v_basinid NUMBER;
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT count( * )
    INTO v_rowcount
    FROM &&tbl_hdb..HDB_SITE
    WHERE UPPER(SITE_COMMON_NAME) = UPPER(IN_STATION_ID);

    IF v_rowcount = 0 THEN
        &&tbl_hdb..DENY_ACTION('Unable to update the site.  STATION_ID does not exist ' || IN_STATION_ID);
    ELSE
        IF v_rowcount <> 1 THEN
            &&tbl_hdb..DENY_ACTION ( 'HDB_SITE.SITE_COMMON_NAME is not unique ' || IN_STATION_ID );
        END IF;

        -- uncomment the following statement to let create new records in &&tbl_hdb..HDB_SITE
        --IF IN_STATE IS NOT NULL THEN
        --    SELECT STATE_ID INTO v_stateid
        --    FROM &&tbl_hdb..HDB_STATE
        --    WHERE STATE_CODE = IN_STATE;
        --ELSE
        --    v_stateid := NULL;
        --END IF;
        --
        --IF IN_LATITUDE IS NOT NULL THEN
        --    v_num := IN_LATITUDE;
        --    SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
        --    INTO v_latitude 
        --    FROM dual;
        --ELSE
        --    v_latitude := NULL;
        --END IF;
        --
        --IF IN_LONGITUDE IS NOT NULL THEN
        --    v_num := IN_LONGITUDE;
        --    SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
        --    INTO v_longitude 
        --    FROM dual;
        --ELSE
        --    v_longitude := NULL;
        --END IF;
        --
        --v_basinid := NULL;      -- !!change this to get correct basin ID!! --

        --UPDATE &&tbl_hdb..HDB_SITE SET
        --    state_id                 = v_stateid,
        --    basin_id                 = v_basinid, 
        --    lat                      = v_latitude, 
        --    longi                    = v_longitude, 
        --    elevation                = IN_ELEVATION, 
        --    description              = IN_SITE_COMMENT    
        --WHERE 
        --    UPPER(SITE_COMMON_NAME)                = UPPER(IN_STATION_ID);  

        UPDATE XC_EXT_SITES SET
            ENABLED = IN_ENABLED,
            UNIT_ID = UPPER(IN_UNIT_ID),
            --IN_SITE_COMMENT,
            INSTALLATION_DATE = IN_INSTALLATION_DATE,
            AGENCY = IN_AGENCY,
            COUNTRY = IN_COUNTRY,
            DISTRICT = IN_DISTRICT,
            CITY = IN_CITY,
            --IN_STATE,
            COUNTY = IN_COUNTY,
            --IN_BASIN,
            --IN_LATITUDE,
            --IN_LONGITUDE,
            --IN_ELEVATION,
            COEFFICIENT1 = IN_COEFFICIENT1,
            COEFFICIENT2 = IN_COEFFICIENT2,
            COEFFICIENT3 = IN_COEFFICIENT3,
            WEIGHTING_FACTOR = IN_WEIGHTING_FACTOR,
            SATELLITE_ID = UPPER(IN_SATELLITE_ID),
            PRIMARY_CHANNEL = IN_PRIMARY_CHANNEL,
            RANDOM_CHANNEL = IN_RANDOM_CHANNEL,
            REPORTING_TIME = IN_REPORTING_TIME,
            REPORTING_INTERVAL = IN_REPORTING_INTERVAL,
            LAST_UPDATE = IN_LAST_UPDATE,
            TZONE_CODE = IN_TZONE_CODE,
            GMT_OFFSET = IN_GMT_OFFSET,
            --IN_SHEF_ID,
            ALTERNATE_CHAR_ID_1 = IN_ALTERNATE_CHAR_ID_1,
            ALTERNATE_CHAR_ID_2 = IN_ALTERNATE_CHAR_ID_2,
            GPSTRING1 = IN_GPSTRING1,
            GPSTRING2 = IN_GPSTRING2,
            GPNUMBER1 = IN_GPNUMBER1,
            GPNUMBER2 = IN_GPNUMBER2,
            SETUP_FILE_NAME = IN_SETUP_FILE_NAME,
            PICTURE_FILE_NAME = IN_PICTURE_FILE_NAME
        WHERE 
            UPPER(STATION_ID) = UPPER(IN_STATION_ID);
        COMMIT;
    END IF;

EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_UPDATE_SITE;
CREATE PUBLIC SYNONYM XC_UPDATE_SITE for &&tbl_owner..XC_UPDATE_SITE;
GRANT EXECUTE ON &&tbl_owner..XC_UPDATE_SITE to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_SITE ;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_SITE ( 
        IN_STATION_ID IN VARCHAR2,
        IN_ENABLED IN VARCHAR2,
        IN_UNIT_ID IN VARCHAR2,
        IN_SITE_COMMENT IN VARCHAR2,
        IN_INSTALLATION_DATE IN DATE,
        IN_AGENCY IN VARCHAR2,
        IN_COUNTRY IN VARCHAR2,
        IN_DISTRICT IN VARCHAR2,
        IN_CITY IN VARCHAR2,
        IN_STATE IN VARCHAR2,
        IN_COUNTY IN VARCHAR2,
        IN_BASIN IN VARCHAR2,
        IN_LATITUDE IN FLOAT,
        IN_LONGITUDE IN FLOAT,
        IN_ELEVATION IN FLOAT,
        IN_COEFFICIENT1 IN FLOAT,
        IN_COEFFICIENT2 IN FLOAT,             
        IN_COEFFICIENT3 IN FLOAT,             
        IN_WEIGHTING_FACTOR IN FLOAT,
        IN_SATELLITE_ID IN VARCHAR2,
        IN_PRIMARY_CHANNEL IN NUMBER,
        IN_RANDOM_CHANNEL IN NUMBER,
        IN_REPORTING_TIME IN DATE,
        IN_REPORTING_INTERVAL IN FLOAT,
        IN_LAST_UPDATE IN DATE,
        IN_TZONE_CODE IN VARCHAR2,
        IN_GMT_OFFSET IN NUMBER,
        IN_SHEF_ID IN VARCHAR2,
        IN_ALTERNATE_CHAR_ID_1 IN VARCHAR2,
        IN_ALTERNATE_CHAR_ID_2 IN VARCHAR2,
        IN_GPSTRING1 IN VARCHAR2,
        IN_GPSTRING2 IN VARCHAR2,
        IN_GPNUMBER1 IN FLOAT,
        IN_GPNUMBER2 IN FLOAT,
        IN_SETUP_FILE_NAME IN VARCHAR2,
        IN_PICTURE_FILE_NAME IN VARCHAR2
        ) 
IS      
    v_rowcount NUMBER;
    v_stateid NUMBER(11);
    v_siteid number(11);
    v_dbsitecode varchar2(3);
    v_latitude varchar2(8);
    v_longitude varchar2(8);
    v_num NUMBER;
    v_basinid NUMBER;
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT count( * )
    INTO v_rowcount
    FROM &&tbl_hdb..HDB_SITE
    WHERE UPPER(SITE_COMMON_NAME) = UPPER(IN_STATION_ID);

    IF v_rowcount = 0 THEN

        -- comment the following statement to let XC_CREATE_SITE create new records in &&tbl_hdb..HDB_SITE
        &&tbl_hdb..DENY_ACTION ( 'XC_CREATE_SITE will not create new records in &&tbl_hdb..HDB_SITE.  The site name must exist in &&tbl_hdb..HDB_SITE.' );

        v_siteid := &&tbl_hdb..populate_pk.get_pk_val('&&tbl_hdb..HDB_SITE', TRUE);

        IF v_siteid IS NULL THEN
          &&tbl_hdb..DENY_ACTION ( 'INVALID <NULL> SITE_ID' );
        END IF;

        IF IN_STATE IS NOT NULL THEN
            SELECT STATE_ID INTO v_stateid
            FROM &&tbl_hdb..HDB_STATE
            WHERE STATE_CODE = IN_STATE;
        ELSE
            v_stateid := NULL;
        END IF;

        IF IN_LATITUDE IS NOT NULL THEN
            v_num := IN_LATITUDE;
            SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
            INTO v_latitude 
            FROM dual;
        ELSE
            v_latitude := NULL;
        END IF;

        IF IN_LONGITUDE IS NOT NULL THEN
            v_num := IN_LONGITUDE;
            SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
            INTO v_longitude 
            FROM dual;
        ELSE
            v_longitude := NULL;
        END IF;

        v_dbsitecode := 'UC'; -- !!change this to get correct default value!! --

        v_basinid := NULL;      -- !!change this to get correct basin ID!! --

        INSERT INTO &&tbl_hdb..HDB_SITE (
            site_id                  , 
            site_name                ,  
            site_common_name         , 
            objecttype_id            , 
            parent_site_id           , 
            parent_objecttype_id     , 
            state_id                 , 
            basin_id                 , 
            lat                      , 
            longi                    , 
            hydrologic_unit          , 
            segment_no               , 
            river_mile               , 
            elevation                , 
            description              ,    
            nws_code                 ,   
            scs_id                   ,  
            shef_code                , 
            usgs_id                  , 
            db_site_code              
        )VALUES(
            v_siteid,                       --   site_id              
            UPPER(IN_STATION_ID),           --   site_name            
            UPPER(IN_STATION_ID),           --   site_common_name     
            9,                              --   objecttype_id        
            NULL,                           --   parent_site_id       
            NULL,                           --   parent_objecttype_id 
            v_stateid,                      --   state_id             
            v_basinid,                      --   basin_id             
            v_latitude,                     --   lat                  
            v_longitude,                    --   longi                
            NULL,                           --   hydrologic_unit      
            NULL,                           --   segment_no           
            NULL,                           --   river_mile           
            IN_ELEVATION,                   --   elevation            
            IN_SITE_COMMENT,                --   description          
            NULL,                           --   nws_code             
            NULL,                           --   scs_id               
            IN_SHEF_ID,                     --   shef_code            
            NULL,                           --   usgs_id              
            v_dbsitecode                    --   db_site_code         
        );

        INSERT INTO XC_EXT_SITES(
            STATION_ID              ,
            ENABLED                 ,
            UNIT_ID                 ,
            INSTALLATION_DATE       ,
            AGENCY                  ,
            COUNTRY                 ,
            DISTRICT                ,
            CITY                    ,
            --STATE                 ,
            COUNTY                  ,
            --BASIN                 ,
            --LATITUDE              ,
            --LONGITUDE             ,
            --ELEVATION             ,
            COEFFICIENT1            ,
            COEFFICIENT2            ,
            COEFFICIENT3            ,
            WEIGHTING_FACTOR        ,
            SATELLITE_ID            ,
            PRIMARY_CHANNEL         ,
            RANDOM_CHANNEL          ,
            REPORTING_TIME          ,
            REPORTING_INTERVAL      ,
            LAST_UPDATE             ,
            TZONE_CODE              ,
            GMT_OFFSET              ,
            ALTERNATE_CHAR_ID_1     ,
            ALTERNATE_CHAR_ID_2     ,
            GPSTRING1               ,
            GPSTRING2               ,
            GPNUMBER1               ,
            GPNUMBER2               ,
            SETUP_FILE_NAME         ,
            PICTURE_FILE_NAME
        )VALUES (
            UPPER(IN_STATION_ID),
            UPPER(IN_ENABLED),
            UPPER(IN_UNIT_ID),
            --IN_SITE_COMMENT,
            IN_INSTALLATION_DATE,
            IN_AGENCY,
            IN_COUNTRY,
            IN_DISTRICT,
            IN_CITY,
            --IN_STATE,
            IN_COUNTY,
            --IN_BASIN,
            --IN_LATITUDE,
            --IN_LONGITUDE,
            --IN_ELEVATION,
            IN_COEFFICIENT1,
            IN_COEFFICIENT2,
            IN_COEFFICIENT3,
            IN_WEIGHTING_FACTOR,
            UPPER(IN_SATELLITE_ID),
            IN_PRIMARY_CHANNEL,
            IN_RANDOM_CHANNEL,
            IN_REPORTING_TIME,
            IN_REPORTING_INTERVAL,
            IN_LAST_UPDATE,
            IN_TZONE_CODE,
            IN_GMT_OFFSET,
            --IN_SHEF_ID,
            IN_ALTERNATE_CHAR_ID_1,
            IN_ALTERNATE_CHAR_ID_2,
            IN_GPSTRING1,
            IN_GPSTRING2,
            IN_GPNUMBER1,
            IN_GPNUMBER2,
            IN_SETUP_FILE_NAME,
            IN_PICTURE_FILE_NAME
        );
	    COMMIT;
    ELSE
        -- Uncomment the following lines to check for HDB_SITE.SITE_COMMON_NAME unique
        -- IF v_rowcount <> 1 THEN
        --     &&tbl_hdb..DENY_ACTION ( 'HDB_SITE.SITE_COMMON_NAME is not unique ' || IN_STATION_ID );
        -- END IF;

        -- uncomment the following statements to update records in &&tbl_hdb..HDB_SITE
        --IF IN_STATE IS NOT NULL THEN
        --    SELECT STATE_ID INTO v_stateid
        --    FROM &&tbl_hdb..HDB_STATE
        --    WHERE STATE_CODE = IN_STATE;
        --ELSE
        --    v_stateid := NULL;
        --END IF;
        --
        --IF IN_LATITUDE IS NOT NULL THEN
        --    v_num := IN_LATITUDE;
        --    SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
        --    INTO v_latitude 
        --    FROM dual;
        --ELSE
        --    v_latitude := NULL;
        --END IF;
        --
        --IF IN_LONGITUDE IS NOT NULL THEN
        --    v_num := IN_LONGITUDE;
        --    SELECT ltrim(rtrim(to_char(trunc(v_num), '000'))) ||  ltrim(rtrim(to_char(trunc((v_num-trunc(v_num))*60), '00'))) || ltrim(rtrim(to_char(trunc((v_num*60-trunc(v_num*60))*60), '00')))
        --    INTO v_longitude 
        --    FROM dual;
        --ELSE
        --    v_longitude := NULL;
        --END IF;
        --
        --v_basinid := NULL;      -- !!change this to get correct basin ID!! --
        --
        --UPDATE &&tbl_hdb..HDB_SITE SET
        --    state_id                 = v_stateid,
        --    basin_id                 = v_basinid, 
        --    lat                      = v_latitude, 
        --    longi                    = v_longitude, 
        --    elevation                = IN_ELEVATION, 
        --    description              = IN_SITE_COMMENT    
        --WHERE 
        --    SITE_COMMON_NAME                = IN_STATION_ID;  

        SELECT COUNT(*) 
        INTO v_rowcount
        FROM XC_EXT_SITES
        WHERE UPPER(STATION_ID) = UPPER(IN_STATION_ID);

        IF v_rowcount = 0 THEN
            INSERT INTO XC_EXT_SITES(
                STATION_ID              ,
                ENABLED                 ,
                UNIT_ID                 ,
                --SITE_COMMENT          ,
                INSTALLATION_DATE       ,
                AGENCY                  ,
                COUNTRY                 ,
                DISTRICT                ,
                CITY                    ,
                --STATE                 ,
                COUNTY                  ,
                --BASIN                 ,
                --LATITUDE              ,
                --LONGITUDE             ,
                --ELEVATION             ,
                COEFFICIENT1            ,
                COEFFICIENT2            ,
                COEFFICIENT3            ,
                WEIGHTING_FACTOR        ,
                SATELLITE_ID            ,
                PRIMARY_CHANNEL         ,
                RANDOM_CHANNEL          ,
                REPORTING_TIME          ,
                REPORTING_INTERVAL      ,
                LAST_UPDATE             ,
                TZONE_CODE              ,
                GMT_OFFSET              ,
                ALTERNATE_CHAR_ID_1     ,
                ALTERNATE_CHAR_ID_2     ,
                GPSTRING1               ,
                GPSTRING2               ,
                GPNUMBER1               ,
                GPNUMBER2               ,
                SETUP_FILE_NAME         ,
                PICTURE_FILE_NAME
            )VALUES (
                UPPER(IN_STATION_ID),
                UPPER(IN_ENABLED),
                UPPER(IN_UNIT_ID),
                --IN_SITE_COMMENT,
                IN_INSTALLATION_DATE,
                IN_AGENCY,
                IN_COUNTRY,
                IN_DISTRICT,
                IN_CITY,
                --IN_STATE,
                IN_COUNTY,
                --IN_BASIN,
                --IN_LATITUDE,
                --IN_LONGITUDE,
                --IN_ELEVATION,
                IN_COEFFICIENT1,
                IN_COEFFICIENT2,
                IN_COEFFICIENT3,
                IN_WEIGHTING_FACTOR,
                UPPER(IN_SATELLITE_ID),
                IN_PRIMARY_CHANNEL,
                IN_RANDOM_CHANNEL,
                IN_REPORTING_TIME,
                IN_REPORTING_INTERVAL,
                IN_LAST_UPDATE,
                IN_TZONE_CODE,
                IN_GMT_OFFSET,
                --IN_SHEF_ID,
                IN_ALTERNATE_CHAR_ID_1,
                IN_ALTERNATE_CHAR_ID_2,
                IN_GPSTRING1,
                IN_GPSTRING2,
                IN_GPNUMBER1,
                IN_GPNUMBER2,
                IN_SETUP_FILE_NAME,
                IN_PICTURE_FILE_NAME
            );
        ELSE
            UPDATE XC_EXT_SITES SET
                ENABLED = IN_ENABLED,
                UNIT_ID = UPPER(IN_UNIT_ID),
                --IN_SITE_COMMENT,
                INSTALLATION_DATE = IN_INSTALLATION_DATE,
                AGENCY = IN_AGENCY,
                COUNTRY = IN_COUNTRY,
                DISTRICT = IN_DISTRICT,
                CITY = IN_CITY,
                --IN_STATE,
                COUNTY = IN_COUNTY,
                --IN_BASIN,
                --IN_LATITUDE,
                --IN_LONGITUDE,
                --IN_ELEVATION,
                COEFFICIENT1 = IN_COEFFICIENT1,
                COEFFICIENT2 = IN_COEFFICIENT2,
                COEFFICIENT3 = IN_COEFFICIENT3,
                WEIGHTING_FACTOR = IN_WEIGHTING_FACTOR,
                SATELLITE_ID = UPPER(IN_SATELLITE_ID),
                PRIMARY_CHANNEL = IN_PRIMARY_CHANNEL,
                RANDOM_CHANNEL = IN_RANDOM_CHANNEL,
                REPORTING_TIME = IN_REPORTING_TIME,
                REPORTING_INTERVAL = IN_REPORTING_INTERVAL,
                LAST_UPDATE = IN_LAST_UPDATE,
                TZONE_CODE = IN_TZONE_CODE,
                GMT_OFFSET = IN_GMT_OFFSET,
                --IN_SHEF_ID,
                ALTERNATE_CHAR_ID_1 = IN_ALTERNATE_CHAR_ID_1,
                ALTERNATE_CHAR_ID_2 = IN_ALTERNATE_CHAR_ID_2,
                GPSTRING1 = IN_GPSTRING1,
                GPSTRING2 = IN_GPSTRING2,
                GPNUMBER1 = IN_GPNUMBER1,
                GPNUMBER2 = IN_GPNUMBER2,
                SETUP_FILE_NAME = IN_SETUP_FILE_NAME,
                PICTURE_FILE_NAME = IN_PICTURE_FILE_NAME
            WHERE 
                UPPER(STATION_ID) = UPPER(IN_STATION_ID);
        END IF;        
        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_SITE;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_SITE for &&tbl_owner..XC_INSERT_OR_UPDATE_SITE;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_SITE to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_UPDATE_LAST_UPDATE;

CREATE PROCEDURE &&tbl_owner..XC_UPDATE_LAST_UPDATE ( 
        IN_STATION_ID IN VARCHAR2,
        IN_LAST_UPDATE IN DATE
        ) 
IS      
BEGIN

    SET TRANSACTION READ WRITE;
    
    BEGIN
        UPDATE XC_EXT_SITES SET
            LAST_UPDATE = IN_LAST_UPDATE
        WHERE 
            UPPER(STATION_ID) = UPPER(IN_STATION_ID) AND ((LAST_UPDATE IS NULL) OR (LAST_UPDATE < IN_LAST_UPDATE));
        
        COMMIT;
    END;

EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_UPDATE_LAST_UPDATE;
CREATE PUBLIC SYNONYM XC_UPDATE_LAST_UPDATE for &&tbl_owner..XC_UPDATE_LAST_UPDATE;
GRANT EXECUTE ON &&tbl_owner..XC_UPDATE_LAST_UPDATE to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_CREATE_SENSOR;
CREATE PROCEDURE &&tbl_owner..XC_CREATE_SENSOR ( 
        IN_STE_STATION_ID IN VARCHAR2             ,
        IN_SENSORNAME IN VARCHAR2                 ,
        IN_ENABLED IN VARCHAR2                    ,
        IN_DATA_TABLE IN VARCHAR2                 ,
        IN_DESCRIPTION IN VARCHAR2                ,
        IN_MANUFACTURER IN VARCHAR2               ,
        IN_MODEL_NUMBER IN VARCHAR2               ,
        IN_SHEF_PE_CODE IN VARCHAR2               ,
        IN_PARAMETER_CODE IN VARCHAR2             ,
        IN_ALT_PARAMETER_CODE_1 IN VARCHAR2       ,
        IN_ALT_PARAMETER_CODE_2 IN VARCHAR2       ,
        IN_EQUATION IN VARCHAR2                   ,
        IN_RATING_TABLE IN VARCHAR2               ,
        IN_OFFSET IN FLOAT                        ,
        IN_SHIFT IN FLOAT                         ,
        IN_RIGHT_DIGIT IN NUMBER                  ,
        IN_HIGH_HIGH_LIMIT IN FLOAT               ,
        IN_HIGH_HIGH_ALARM IN NUMBER              ,
        IN_HIGH_LIMIT IN FLOAT                    ,
        IN_HIGH_ALARM IN NUMBER                   ,
        IN_LOW_LIMIT IN FLOAT                     ,
        IN_LOW_ALARM IN NUMBER                    ,
        IN_LOW_LOW_LIMIT IN FLOAT                 ,
        IN_LOW_LOW_ALARM IN NUMBER                ,
        IN_ROC_LIMIT IN FLOAT                     ,
        IN_ROC_ALARM IN NUMBER                    ,
        IN_ROC_INTERVAL IN NUMBER                 ,
        IN_DEADBAND IN FLOAT                      ,
        IN_NO_CHANGE_INTERVAL IN FLOAT            ,
        IN_NO_CHANGE_ALARM IN NUMBER              ,
        IN_TEXT_LIMIT IN VARCHAR2                 ,
        IN_TEXT_ALARM IN NUMBER                   ,
        IN_COEFFICIENT1 IN FLOAT                  ,
        IN_COEFFICIENT2 IN FLOAT                  ,             
        IN_COEFFICIENT3 IN FLOAT                  ,             
        IN_COEFFICIENT4 IN FLOAT                  ,
        IN_COEFFICIENT5 IN FLOAT                  ,             
        IN_GPNUMBER1 IN FLOAT                     ,
        IN_GPNUMBER2 IN FLOAT                     ,
        IN_GPSTRING1 IN VARCHAR2                  ,
        IN_GPSTRING2 IN VARCHAR2                  ,
        IN_ALERT_ID IN NUMBER                     ,
        IN_ALERT_TYPE IN VARCHAR2   
        )                                      
IS                                          
    v_datatype_id NUMBER;
    v_rowcount NUMBER;
    v_siteid NUMBER;
    v_site_datatype_id NUMBER;
    v_interval VARCHAR2(16);
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_EXT_SITESENSORS
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STE_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);
    
    IF v_rowcount > 0 THEN
        &&tbl_hdb..DENY_ACTION ( 'Duplicate Sensor Name ' || IN_SENSORNAME);
    END IF;

    v_site_datatype_id := NULL;
    v_interval := 'instant';

    -- !!Get the proper DATATYPE_ID !! --

    -- In production system, DataType_ID and Interval will be NULL;
    -- In debug system, we try to get the proper value
    -- As we don't know any info about DATATYPE_ID from XCONNECT, we will try to get the next available DATATYPE_ID
    -- DATATYPE_ID must be an instant type
    -- This associate in &&tbl_hdb..HDB_SITE_DATATYPE can be change in HDB later
    -- NOTE: (SITE_ID, DATATYPE_ID) must be unique
    
    --!!!BEGIN DEBUG 
    --SELECT MIN(DATATYPE_ID)
    --INTO v_datatype_id
    --FROM &&tbl_hdb..HDB_DATATYPE A
    --WHERE 
    --    (
    --        A.datatype_type like '%simple%' OR
    --        A.method_class_id = 1 OR
    --        (
    --            A.method_class_id in (11,12,13) AND 
    --            A.method_source_datatype_id IN 
    --            (
    --                 SELECT datatype_id
    --                 FROM &&tbl_hdb..HDB_DATATYPE
    --                 WHERE 
    --                    datatype_type LIKE '%simple%' OR
    --                    method_class_id =1
    --            )
    --        ) 
    --    ) AND     
    --    NOT EXISTS (
    --        SELECT * 
    --        FROM &&tbl_hdb..HDB_SITE_DATATYPE B JOIN &&tbl_hdb..HDB_SITE C ON B.SITE_ID = C.SITE_ID
    --        WHERE UPPER(C.SITE_COMMON_NAME) = UPPER(IN_STE_STATION_ID) AND A.DATATYPE_ID = B.DATATYPE_ID
    --        );
    --
    --IF v_datatype_id IS NULL THEN
    --    &&tbl_hdb..DENY_ACTION ( 'INVALID <NULL> DATATYPE_ID' );
    --END IF;
    --
    IF v_rowcount = 0 THEN
    --
    --    v_site_datatype_id := &&tbl_hdb..populate_pk.get_pk_val('&&tbl_hdb..HDB_SITE_DATATYPE', TRUE);
    --
    --    SELECT SITE_ID 
    --    INTO v_siteid 
    --    FROM &&tbl_hdb..HDB_SITE
    --    WHERE UPPER(SITE_COMMON_NAME) = UPPER(IN_STE_STATION_ID);
    --
    --    IF v_siteid IS NULL THEN
    --        &&tbl_hdb..DENY_ACTION ( 'INVALID <NULL> SITE_ID' );
    --    END IF;
    --
    --    INSERT INTO &&tbl_hdb..HDB_SITE_DATATYPE (
    --        site_id                  , 
    --        datatype_id              ,           
    --        site_datatype_id
    --    )VALUES(
    --        v_siteid,
    --        v_datatype_id,
    --        v_site_datatype_id
    --    );
    --!!!END DEBUG

        INSERT INTO XC_EXT_SITESENSORS (
            STE_STATION_ID      ,
            SENSORNAME          ,
            ENABLED             ,
            DATA_TABLE          ,
            DESCRIPTION         ,
            MANUFACTURER        ,
            MODEL_NUMBER        ,
            SHEF_PE_CODE        ,
            PARAMETER_CODE      ,
            ALT_PARAMETER_CODE_1, 
            ALT_PARAMETER_CODE_2, 
            EQUATION            ,
            RATING_TABLE        ,
            OFFSET              ,
            SHIFT               ,
            RIGHT_DIGIT         ,
            HIGH_HIGH_LIMIT     ,
            HIGH_HIGH_ALARM     ,
            HIGH_LIMIT          ,
            HIGH_ALARM          ,
            LOW_LIMIT           ,
            LOW_ALARM           ,
            LOW_LOW_LIMIT       ,
            LOW_LOW_ALARM       ,
            ROC_LIMIT           ,
            ROC_ALARM           ,
            ROC_INTERVAL        ,
            DEADBAND            ,
            NO_CHANGE_INTERVAL  ,
            NO_CHANGE_ALARM     ,
            TEXT_LIMIT          ,
            TEXT_ALARM          ,
            COEFFICIENT1        ,
            COEFFICIENT2        ,
            COEFFICIENT3        ,
            COEFFICIENT4        ,
            COEFFICIENT5        ,
            GPNUMBER1           ,
            GPNUMBER2           ,
            GPSTRING1           ,
            GPSTRING2           ,
            ALERT_ID            ,
            ALERT_TYPE          ,
            SITE_DATATYPE_ID    ,
            INTERVAL
        )VALUES (
            UPPER(IN_STE_STATION_ID),
            UPPER(IN_SENSORNAME)   ,
            UPPER(IN_ENABLED)      ,
            IN_DATA_TABLE          ,
            IN_DESCRIPTION         ,
            IN_MANUFACTURER        ,
            IN_MODEL_NUMBER        ,
            IN_SHEF_PE_CODE        ,
            IN_PARAMETER_CODE      ,
            IN_ALT_PARAMETER_CODE_1, 
            IN_ALT_PARAMETER_CODE_2, 
            IN_EQUATION            ,
            IN_RATING_TABLE        ,
            IN_OFFSET              ,
            IN_SHIFT               ,
            IN_RIGHT_DIGIT         ,
            IN_HIGH_HIGH_LIMIT     ,
            IN_HIGH_HIGH_ALARM     ,
            IN_HIGH_LIMIT          ,
            IN_HIGH_ALARM          ,
            IN_LOW_LIMIT           ,
            IN_LOW_ALARM           ,
            IN_LOW_LOW_LIMIT       ,
            IN_LOW_LOW_ALARM       ,
            IN_ROC_LIMIT           ,
            IN_ROC_ALARM           ,
            IN_ROC_INTERVAL        ,
            IN_DEADBAND            ,
            IN_NO_CHANGE_INTERVAL  ,
            IN_NO_CHANGE_ALARM     ,
            IN_TEXT_LIMIT          ,
            IN_TEXT_ALARM          ,
            IN_COEFFICIENT1        ,
            IN_COEFFICIENT2        ,
            IN_COEFFICIENT3        ,
            IN_COEFFICIENT4        ,
            IN_COEFFICIENT5        ,
            IN_GPNUMBER1           ,
            IN_GPNUMBER2           ,
            IN_GPSTRING1           ,
            IN_GPSTRING2           ,
            IN_ALERT_ID            ,
            IN_ALERT_TYPE          ,
            v_site_datatype_id     ,
            v_interval
        ); 

        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_CREATE_SENSOR;
CREATE PUBLIC SYNONYM XC_CREATE_SENSOR for &&tbl_owner..XC_CREATE_SENSOR;
GRANT EXECUTE ON &&tbl_owner..XC_CREATE_SENSOR to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_UPDATE_SENSOR;
CREATE PROCEDURE &&tbl_owner..XC_UPDATE_SENSOR ( 
        IN_STE_STATION_ID IN VARCHAR2             ,
        IN_SENSORNAME IN VARCHAR2                 ,
        IN_ENABLED IN VARCHAR2                    ,
        IN_DATA_TABLE IN VARCHAR2                 ,
        IN_DESCRIPTION IN VARCHAR2                ,
        IN_MANUFACTURER IN VARCHAR2               ,
        IN_MODEL_NUMBER IN VARCHAR2               ,
        IN_SHEF_PE_CODE IN VARCHAR2               ,
        IN_PARAMETER_CODE IN VARCHAR2             ,
        IN_ALT_PARAMETER_CODE_1 IN VARCHAR2       ,
        IN_ALT_PARAMETER_CODE_2 IN VARCHAR2       ,
        IN_EQUATION IN VARCHAR2                   ,
        IN_RATING_TABLE IN VARCHAR2               ,
        IN_OFFSET IN FLOAT                        ,
        IN_SHIFT IN FLOAT                         ,
        IN_RIGHT_DIGIT IN NUMBER                  ,
        IN_HIGH_HIGH_LIMIT IN FLOAT               ,
        IN_HIGH_HIGH_ALARM IN NUMBER              ,
        IN_HIGH_LIMIT IN FLOAT                    ,
        IN_HIGH_ALARM IN NUMBER                   ,
        IN_LOW_LIMIT IN FLOAT                     ,
        IN_LOW_ALARM IN NUMBER                    ,
        IN_LOW_LOW_LIMIT IN FLOAT                 ,
        IN_LOW_LOW_ALARM IN NUMBER                ,
        IN_ROC_LIMIT IN FLOAT                     ,
        IN_ROC_ALARM IN NUMBER                    ,
        IN_ROC_INTERVAL IN NUMBER                 ,
        IN_DEADBAND IN FLOAT                      ,
        IN_NO_CHANGE_INTERVAL IN FLOAT            ,
        IN_NO_CHANGE_ALARM IN NUMBER              ,
        IN_TEXT_LIMIT IN VARCHAR2                 ,
        IN_TEXT_ALARM IN NUMBER                   ,
        IN_COEFFICIENT1 IN FLOAT                  ,  
        IN_COEFFICIENT2 IN FLOAT                  ,
        IN_COEFFICIENT3 IN FLOAT                  ,
        IN_COEFFICIENT4 IN FLOAT                  ,
        IN_COEFFICIENT5 IN FLOAT                  ,
        IN_GPNUMBER1 IN FLOAT                     ,
        IN_GPNUMBER2 IN FLOAT                     ,
        IN_GPSTRING1 IN VARCHAR2                  ,
        IN_GPSTRING2 IN VARCHAR2                  ,
        IN_ALERT_ID IN NUMBER                     ,
        IN_ALERT_TYPE IN VARCHAR2                 
        )                                      
IS                                          
    v_rowcount NUMBER;
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_EXT_SITESENSORS
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STE_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);
    
    IF v_rowcount <> 1 THEN
        &&tbl_hdb..DENY_ACTION ( 'Sensor not found ' || IN_SENSORNAME);
    END IF;

    BEGIN
        UPDATE XC_EXT_SITESENSORS SET 
            ENABLED                  = IN_ENABLED             ,
            DATA_TABLE               = IN_DATA_TABLE          ,
            DESCRIPTION              = IN_DESCRIPTION         ,
            MANUFACTURER             = IN_MANUFACTURER        ,
            MODEL_NUMBER             = IN_MODEL_NUMBER        ,
            SHEF_PE_CODE             = IN_SHEF_PE_CODE        ,
            PARAMETER_CODE           = IN_PARAMETER_CODE      ,
            ALT_PARAMETER_CODE_1     = IN_ALT_PARAMETER_CODE_1, 
            ALT_PARAMETER_CODE_2     = IN_ALT_PARAMETER_CODE_2, 
            EQUATION                 = IN_EQUATION            ,
            RATING_TABLE             = IN_RATING_TABLE        ,
            OFFSET                   = IN_OFFSET              ,
            SHIFT                    = IN_SHIFT               ,
            RIGHT_DIGIT              = IN_RIGHT_DIGIT         ,
            HIGH_HIGH_LIMIT          = IN_HIGH_HIGH_LIMIT     ,
            HIGH_HIGH_ALARM          = IN_HIGH_HIGH_ALARM     ,
            HIGH_LIMIT               = IN_HIGH_LIMIT          ,
            HIGH_ALARM               = IN_HIGH_ALARM          ,
            LOW_LIMIT                = IN_LOW_LIMIT           ,
            LOW_ALARM                = IN_LOW_ALARM           ,
            LOW_LOW_LIMIT            = IN_LOW_LOW_LIMIT       ,
            LOW_LOW_ALARM            = IN_LOW_LOW_ALARM       ,
            ROC_LIMIT                = IN_ROC_LIMIT           ,
            ROC_ALARM                = IN_ROC_ALARM           ,
            ROC_INTERVAL             = IN_ROC_INTERVAL        ,
            DEADBAND                 = IN_DEADBAND            ,
            NO_CHANGE_INTERVAL       = IN_NO_CHANGE_INTERVAL  ,
            NO_CHANGE_ALARM          = IN_NO_CHANGE_ALARM     ,
            TEXT_LIMIT               = IN_TEXT_LIMIT          ,
            TEXT_ALARM               = IN_TEXT_ALARM          ,
            COEFFICIENT1             = IN_COEFFICIENT1        ,  
            COEFFICIENT2             = IN_COEFFICIENT2        ,
            COEFFICIENT3             = IN_COEFFICIENT3        ,
            COEFFICIENT4             = IN_COEFFICIENT4        ,
            COEFFICIENT5             = IN_COEFFICIENT5        ,
            GPNUMBER1                = IN_GPNUMBER1           ,
            GPNUMBER2                = IN_GPNUMBER2           ,
            GPSTRING1                = IN_GPSTRING1           ,
            GPSTRING2                = IN_GPSTRING2           ,
            ALERT_ID                 = IN_ALERT_ID            ,
            ALERT_TYPE               = IN_ALERT_TYPE          
        WHERE
            UPPER(STE_STATION_ID)           = UPPER(IN_STE_STATION_ID)      AND
            UPPER(SENSORNAME)               = UPPER(IN_SENSORNAME)          ;

        COMMIT;
    END;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_UPDATE_SENSOR;
CREATE PUBLIC SYNONYM XC_UPDATE_SENSOR for &&tbl_owner..XC_UPDATE_SENSOR;
GRANT EXECUTE ON &&tbl_owner..XC_UPDATE_SENSOR to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_SENSOR;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_SENSOR ( 
        IN_STE_STATION_ID IN VARCHAR2             ,
        IN_SENSORNAME IN VARCHAR2                 ,
        IN_ENABLED IN VARCHAR2                    ,
        IN_DATA_TABLE IN VARCHAR2                 ,
        IN_DESCRIPTION IN VARCHAR2                ,
        IN_MANUFACTURER IN VARCHAR2               ,
        IN_MODEL_NUMBER IN VARCHAR2               ,
        IN_SHEF_PE_CODE IN VARCHAR2               ,
        IN_PARAMETER_CODE IN VARCHAR2             ,
        IN_ALT_PARAMETER_CODE_1 IN VARCHAR2       ,
        IN_ALT_PARAMETER_CODE_2 IN VARCHAR2       ,
        IN_EQUATION IN VARCHAR2                   ,
        IN_RATING_TABLE IN VARCHAR2               ,
        IN_OFFSET IN FLOAT                        ,
        IN_SHIFT IN FLOAT                         ,
        IN_RIGHT_DIGIT IN NUMBER                  ,
        IN_HIGH_HIGH_LIMIT IN FLOAT               ,
        IN_HIGH_HIGH_ALARM IN NUMBER              ,
        IN_HIGH_LIMIT IN FLOAT                    ,
        IN_HIGH_ALARM IN NUMBER                   ,
        IN_LOW_LIMIT IN FLOAT                     ,
        IN_LOW_ALARM IN NUMBER                    ,
        IN_LOW_LOW_LIMIT IN FLOAT                 ,
        IN_LOW_LOW_ALARM IN NUMBER                ,
        IN_ROC_LIMIT IN FLOAT                     ,
        IN_ROC_ALARM IN NUMBER                    ,
        IN_ROC_INTERVAL IN NUMBER                 ,
        IN_DEADBAND IN FLOAT                      ,
        IN_NO_CHANGE_INTERVAL IN FLOAT            ,
        IN_NO_CHANGE_ALARM IN NUMBER              ,
        IN_TEXT_LIMIT IN VARCHAR2                 ,
        IN_TEXT_ALARM IN NUMBER                   ,
        IN_COEFFICIENT1 IN FLOAT                  ,
        IN_COEFFICIENT2 IN FLOAT                  ,
        IN_COEFFICIENT3 IN FLOAT                  ,
        IN_COEFFICIENT4 IN FLOAT                  ,
        IN_COEFFICIENT5 IN FLOAT                  ,
        IN_GPNUMBER1 IN FLOAT                     ,
        IN_GPNUMBER2 IN FLOAT                     ,
        IN_GPSTRING1 IN VARCHAR2                  ,
        IN_GPSTRING2 IN VARCHAR2                  ,
        IN_ALERT_ID IN NUMBER                     ,
        IN_ALERT_TYPE IN VARCHAR2                 
        )                                      
IS                                          
    v_datatype_id NUMBER;
    v_rowcount NUMBER;
    v_siteid NUMBER;
    v_site_datatype_id NUMBER;
    v_interval VARCHAR2(16);
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_EXT_SITESENSORS
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STE_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);

    v_site_datatype_id := NULL;
    v_interval := 'instant';
    
    IF v_rowcount = 0 THEN
        -- In production system, DataType_ID and Interval will be NULL;
        -- In debug system, we try to get the proper value
        -- !!Get the proper DATATYPE_ID !! --
        -- As we don't know any info about DATATYPE_ID from XCONNECT, we will try to get the next available DATATYPE_ID
        -- DATATYPE_ID must be an instant type
        -- This associate in &&tbl_hdb..HDB_SITE_DATATYPE can be change in HDB later
        -- NOTE: (SITE_ID, DATATYPE_ID) must be unique

        --!!!BEGIN DEBUG
        --SELECT MIN(DATATYPE_ID)
        --INTO v_datatype_id
        --FROM &&tbl_hdb..HDB_DATATYPE A
        --WHERE 
        --    (
        --        A.datatype_type like '%simple%' OR
        --        A.method_class_id = 1 OR
        --        (
        --            A.method_class_id in (11,12,13) AND 
        --            A.method_source_datatype_id IN 
        --            (
        --                 SELECT datatype_id
        --                 FROM &&tbl_hdb..HDB_DATATYPE
        --                 WHERE 
        --                    datatype_type LIKE '%simple%' OR
        --                    method_class_id =1
        --            )
        --        ) 
        --    ) AND     
        --    NOT EXISTS (
        --        SELECT * 
        --        FROM &&tbl_hdb..HDB_SITE_DATATYPE B JOIN &&tbl_hdb..HDB_SITE C ON B.SITE_ID = C.SITE_ID
        --        WHERE UPPER(C.SITE_COMMON_NAME) = UPPER(IN_STE_STATION_ID) AND A.DATATYPE_ID = B.DATATYPE_ID
        --        );
        --
        --IF v_datatype_id IS NULL THEN
        --    &&tbl_hdb..DENY_ACTION ( 'INVALID <NULL> DATATYPE_ID' );
        --END IF;
        --
        --v_site_datatype_id := &&tbl_hdb..populate_pk.get_pk_val('&&tbl_hdb..HDB_SITE_DATATYPE', TRUE);
        --
        --SELECT SITE_ID 
        --INTO v_siteid 
        --FROM &&tbl_hdb..HDB_SITE
        --WHERE UPPER(SITE_COMMON_NAME) = UPPER(IN_STE_STATION_ID);
        --
        --IF v_siteid IS NULL THEN
        --    &&tbl_hdb..DENY_ACTION ( 'INVALID <NULL> SITE_ID' );
        --END IF;
        --
        --INSERT INTO &&tbl_hdb..HDB_SITE_DATATYPE (
        --    site_id                  , 
        --    datatype_id              ,           
        --    site_datatype_id
        --)VALUES(
        --    v_siteid,
        --    v_datatype_id,
        --    v_site_datatype_id
        --);
        --!!!END DEBUG

        INSERT INTO XC_EXT_SITESENSORS (
            STE_STATION_ID      ,
            SENSORNAME          ,
            ENABLED             ,
            DATA_TABLE          ,
            DESCRIPTION         ,
            MANUFACTURER        ,
            MODEL_NUMBER        ,
            SHEF_PE_CODE        ,
            PARAMETER_CODE      ,
            ALT_PARAMETER_CODE_1, 
            ALT_PARAMETER_CODE_2, 
            EQUATION            ,
            RATING_TABLE        ,
            OFFSET              ,
            SHIFT               ,
            RIGHT_DIGIT         ,
            HIGH_HIGH_LIMIT     ,
            HIGH_HIGH_ALARM     ,
            HIGH_LIMIT          ,
            HIGH_ALARM          ,
            LOW_LIMIT           ,
            LOW_ALARM           ,
            LOW_LOW_LIMIT       ,
            LOW_LOW_ALARM       ,
            ROC_LIMIT           ,
            ROC_ALARM           ,
            ROC_INTERVAL        ,
            DEADBAND            ,
            NO_CHANGE_INTERVAL  ,
            NO_CHANGE_ALARM     ,
            TEXT_LIMIT          ,
            TEXT_ALARM          ,
            COEFFICIENT1        ,
            COEFFICIENT2        ,
            COEFFICIENT3        ,
            COEFFICIENT4        ,
            COEFFICIENT5        ,
            GPNUMBER1           ,
            GPNUMBER2           ,
            GPSTRING1           ,
            GPSTRING2           ,
            ALERT_ID            ,
            ALERT_TYPE          ,
            SITE_DATATYPE_ID    ,
            INTERVAL
        )VALUES (
            UPPER(IN_STE_STATION_ID)      ,
            UPPER(IN_SENSORNAME)          ,
            UPPER(IN_ENABLED)             ,
            IN_DATA_TABLE          ,
            IN_DESCRIPTION         ,
            IN_MANUFACTURER        ,
            IN_MODEL_NUMBER        ,
            IN_SHEF_PE_CODE        ,
            IN_PARAMETER_CODE      ,
            IN_ALT_PARAMETER_CODE_1, 
            IN_ALT_PARAMETER_CODE_2, 
            IN_EQUATION            ,
            IN_RATING_TABLE        ,
            IN_OFFSET              ,
            IN_SHIFT               ,
            IN_RIGHT_DIGIT         ,
            IN_HIGH_HIGH_LIMIT     ,
            IN_HIGH_HIGH_ALARM     ,
            IN_HIGH_LIMIT          ,
            IN_HIGH_ALARM          ,
            IN_LOW_LIMIT           ,
            IN_LOW_ALARM           ,
            IN_LOW_LOW_LIMIT       ,
            IN_LOW_LOW_ALARM       ,
            IN_ROC_LIMIT           ,
            IN_ROC_ALARM           ,
            IN_ROC_INTERVAL        ,
            IN_DEADBAND            ,
            IN_NO_CHANGE_INTERVAL  ,
            IN_NO_CHANGE_ALARM     ,
            IN_TEXT_LIMIT          ,
            IN_TEXT_ALARM          ,
            IN_COEFFICIENT1        ,
            IN_COEFFICIENT2        ,
            IN_COEFFICIENT3        ,
            IN_COEFFICIENT4        ,
            IN_COEFFICIENT5        ,
            IN_GPNUMBER1           ,
            IN_GPNUMBER2           ,
            IN_GPSTRING1           ,
            IN_GPSTRING2           ,
            IN_ALERT_ID            ,
            IN_ALERT_TYPE          ,
            v_site_datatype_id     ,
            v_interval
        ); 

        COMMIT;
    ELSE
        UPDATE XC_EXT_SITESENSORS SET 
            ENABLED                  = IN_ENABLED             ,
            DATA_TABLE               = IN_DATA_TABLE          ,
            DESCRIPTION              = IN_DESCRIPTION         ,
            MANUFACTURER             = IN_MANUFACTURER        ,
            MODEL_NUMBER             = IN_MODEL_NUMBER        ,
            SHEF_PE_CODE             = IN_SHEF_PE_CODE        ,
            PARAMETER_CODE           = IN_PARAMETER_CODE      ,
            ALT_PARAMETER_CODE_1     = IN_ALT_PARAMETER_CODE_1, 
            ALT_PARAMETER_CODE_2     = IN_ALT_PARAMETER_CODE_2, 
            EQUATION                 = IN_EQUATION            ,
            RATING_TABLE             = IN_RATING_TABLE        ,
            OFFSET                   = IN_OFFSET              ,
            SHIFT                    = IN_SHIFT               ,
            RIGHT_DIGIT              = IN_RIGHT_DIGIT         ,
            HIGH_HIGH_LIMIT          = IN_HIGH_HIGH_LIMIT     ,
            HIGH_HIGH_ALARM          = IN_HIGH_HIGH_ALARM     ,
            HIGH_LIMIT               = IN_HIGH_LIMIT          ,
            HIGH_ALARM               = IN_HIGH_ALARM          ,
            LOW_LIMIT                = IN_LOW_LIMIT           ,
            LOW_ALARM                = IN_LOW_ALARM           ,
            LOW_LOW_LIMIT            = IN_LOW_LOW_LIMIT       ,
            LOW_LOW_ALARM            = IN_LOW_LOW_ALARM       ,
            ROC_LIMIT                = IN_ROC_LIMIT           ,
            ROC_ALARM                = IN_ROC_ALARM           ,
            ROC_INTERVAL             = IN_ROC_INTERVAL        ,
            DEADBAND                 = IN_DEADBAND            ,
            NO_CHANGE_INTERVAL       = IN_NO_CHANGE_INTERVAL  ,
            NO_CHANGE_ALARM          = IN_NO_CHANGE_ALARM     ,
            TEXT_LIMIT               = IN_TEXT_LIMIT          ,
            TEXT_ALARM               = IN_TEXT_ALARM          ,
            COEFFICIENT1             = IN_COEFFICIENT1        ,  
            COEFFICIENT2             = IN_COEFFICIENT2        ,
            COEFFICIENT3             = IN_COEFFICIENT3        ,
            COEFFICIENT4             = IN_COEFFICIENT4        ,
            COEFFICIENT5             = IN_COEFFICIENT5        ,
            GPNUMBER1                = IN_GPNUMBER1           ,
            GPNUMBER2                = IN_GPNUMBER2           ,
            GPSTRING1                = IN_GPSTRING1           ,
            GPSTRING2                = IN_GPSTRING2           ,
            ALERT_ID                 = IN_ALERT_ID            ,
            ALERT_TYPE               = IN_ALERT_TYPE          
        WHERE
            UPPER(STE_STATION_ID)           = UPPER(IN_STE_STATION_ID)      AND
            UPPER(SENSORNAME)               = UPPER(IN_SENSORNAME)          ;

        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_SENSOR;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_SENSOR for &&tbl_owner..XC_INSERT_OR_UPDATE_SENSOR;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_SENSOR to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_GET_DATA_DEFAULT;

CREATE PROCEDURE &&tbl_owner..XC_GET_DATA_DEFAULT ( 
    site_datatype_id        IN NUMBER       ,
    interval                IN VARCHAR2     ,
    overwrite_flag          OUT VARCHAR2    ,
    validation              OUT CHAR        ,  
    agen_id                 OUT NUMBER      ,
    collection_system_id    OUT  NUMBER     ,
    loading_application_id  OUT  NUMBER     ,
    method_id               OUT NUMBER      ,
    computation_id          OUT NUMBER      ,
    do_update_y_or_n        OUT VARCHAR2
   )
IS
BEGIN
    overwrite_flag          :=  NULL;
    validation              :=  'Z';    
    do_update_y_or_n        :=  'Y';  

    &&tbl_hdb..lookup_application(
        'See loading application',      --AGEN_NAME  
        'See loading application',      --COLLECTION_SYSTEM_NAME 
        'XConnect',                     --LOADING_APPLICATION_NAME 
        'unknown',                      --METHOD_NAME 
        'N/A',                          --COMPUTATION_NAME 
        agen_id,                        --AGEN_ID IN 
        collection_system_id,           --COLLECTION_SYSTEM_ID 
        loading_application_id,         --LOADING_APPLICATION_ID 
        method_id,                      --METHOD_ID 
        computation_id                  --COMPUTATION_ID 
      );
END;
/
show errors;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_DATA;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_DATA ( 
            IN_STATION_ID     IN VARCHAR2 ,
            IN_SENSORNAME     IN VARCHAR2 ,
            IN_TIME_TAG       IN DATE     ,
            IN_SOURCE         IN VARCHAR2 ,
            IN_ORIG_VALUE     IN FLOAT    ,
            IN_ED_VALUE       IN FLOAT    ,
            IN_FLAG1          IN VARCHAR2 ,
            IN_FLAG2          IN VARCHAR2 ,
            IN_FLAG3          IN VARCHAR2 ,
            IN_FLAG4          IN VARCHAR2 ,
            IN_HIGH_HIGH_FLAG IN NUMBER   ,
            IN_HIGH_FLAG      IN NUMBER   ,
            IN_LOW_FLAG       IN NUMBER   ,
            IN_LOW_LOW_FLAG   IN NUMBER   ,
            IN_ROC_FLAG       IN NUMBER   ,
            IN_NO_CHG_FLAG    IN NUMBER   ,
            IN_ALARM_FLAG     IN NUMBER
        )                                      
IS                                          
    v_rowcount          NUMBER;
    v_site_datatype_id  NUMBER;
    v_interval          VARCHAR2(16);
    v_overwrite_flag    VARCHAR2(1);         
    v_validation        CHAR;             
    v_agen_id           NUMBER;     
    v_collection_system_id      NUMBER;   
    v_loading_application_id    NUMBER;
    v_method_id         NUMBER;
    v_computation_id    NUMBER;     
    v_do_update_y_or_n  VARCHAR2(1);     
    v_start_date_time	DATE;
    v_end_date_time     DATE;
BEGIN

    SET TRANSACTION READ WRITE;

    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_EXT_SITESENSORS 
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);

    IF v_rowcount = 0 THEN
        &&tbl_hdb..DENY_ACTION ( 'Site Data Type ID does not exist: ' || IN_STATION_ID || '.' || IN_SENSORNAME);
    END IF;

    SELECT SITE_DATATYPE_ID, INTERVAL
    INTO v_site_datatype_id, v_interval
    FROM XC_EXT_SITESENSORS 
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);

    IF (v_site_datatype_id IS NULL) OR (v_interval IS NULL) THEN
        &&tbl_hdb..DENY_ACTION ( 'site_datatype_id or interval must not be NULL: ' || IN_STATION_ID || '.' || IN_SENSORNAME);
    END IF;

    &&tbl_owner..XC_GET_DATA_DEFAULT(
        v_site_datatype_id          ,
        v_interval                  ,
        v_overwrite_flag            ,
        v_validation                ,
        v_agen_id                   ,
        v_collection_system_id      ,
        v_loading_application_id    ,
        v_method_id                 ,
        v_computation_id            ,
        v_do_update_y_or_n          
    );

    v_start_date_time := IN_TIME_TAG;
    v_end_date_time := NULL;

    -- BEGIN Boulder HDB ONLY
    -- For all hourly data, as the data logger log the hourly data at the end of the hour
    -- make adjustment
    -- IF v_interval = 'hour' THEN 
    --     v_start_date_time := IN_TIME_TAG - 1/24;
    --     v_end_date_time := IN_TIME_TAG;
    -- END IF;
    -- END Boulder HDB ONLY

    &&tbl_hdb..modify_r_base_raw(
        v_site_datatype_id,     --SITE_DATATYPE_ID
        v_interval,             --INTERVAL            
        v_start_date_time,      --START_DATE_TIME         
        v_end_date_time,        --END_DATE_TIME           
        IN_ED_VALUE,            --VALUE                   
        v_agen_id,              --AGEN_ID                 
        v_overwrite_flag,       --OVERWRITE_FLAG          
        v_validation,           --VALIDATION              
        v_collection_system_id, --COLLECTION_SYSTEM_ID    
        v_loading_application_id,--LOADING_APPLICATION_ID  
        v_method_id,            --METHOD_ID               
        v_computation_id,       --COMPUTATION_ID          
        v_do_update_y_or_n      --DO_UPDATE_Y_OR_N        
    );

    SELECT COUNT(*) INTO v_rowcount
    FROM XC_EXT_DATA1
    WHERE 
            site_datatype_id  =  v_site_datatype_id AND 
            interval          =  v_interval         AND
            start_date_time   =  v_start_date_time  AND  
            end_date_time     =  v_end_date_time    AND     
            SOURCE            =  IN_SOURCE          ;        

    IF v_rowcount = 0 THEN
        INSERT INTO XC_EXT_DATA1(
            site_datatype_id,
            interval       ,
            start_date_time,
            end_date_time  ,
            SOURCE         ,            
            ORIG_VALUE     ,            
            FLAG1          ,            
            FLAG2          ,            
            FLAG3          ,            
            FLAG4          ,            
            HIGH_HIGH_FLAG ,            
            HIGH_FLAG      ,            
            LOW_FLAG       ,            
            LOW_LOW_FLAG   ,            
            ROC_FLAG       ,            
            NO_CHG_FLAG    ,            
            ALARM_FLAG                  
        )VALUES(
            v_site_datatype_id,         --site_datatype_id
            v_interval        ,         --interval        
            v_start_date_time ,         --start_date_time 
            v_end_date_time   ,         --end_date_time   
            IN_SOURCE         ,         --SOURCE          
            IN_ORIG_VALUE     ,         --ORIG_VALUE         
            IN_FLAG1          ,         --FLAG1              
            IN_FLAG2          ,         --FLAG2              
            IN_FLAG3          ,         --FLAG3              
            IN_FLAG4          ,         --FLAG4              
            IN_HIGH_HIGH_FLAG ,         --HIGH_HIGH_FLAG     
            IN_HIGH_FLAG      ,         --HIGH_FLAG          
            IN_LOW_FLAG       ,         --LOW_FLAG           
            IN_LOW_LOW_FLAG   ,         --LOW_LOW_FLAG       
            IN_ROC_FLAG       ,         --ROC_FLAG           
            IN_NO_CHG_FLAG    ,         --NO_CHG_FLAG        
            IN_ALARM_FLAG               --ALARM_FLAG          
        );

        COMMIT;
    ELSE
        UPDATE XC_EXT_DATA1 SET
            FLAG1             =  IN_FLAG1          ,        
            FLAG2             =  IN_FLAG2          ,        
            FLAG3             =  IN_FLAG3          ,        
            FLAG4             =  IN_FLAG4          ,        
            HIGH_HIGH_FLAG    =  IN_HIGH_HIGH_FLAG ,        
            HIGH_FLAG         =  IN_HIGH_FLAG      ,        
            LOW_FLAG          =  IN_LOW_FLAG       ,        
            LOW_LOW_FLAG      =  IN_LOW_LOW_FLAG   ,        
            ROC_FLAG          =  IN_ROC_FLAG       ,        
            NO_CHG_FLAG       =  IN_NO_CHG_FLAG    ,        
            ALARM_FLAG        =  IN_ALARM_FLAG               
        WHERE
            site_datatype_id  =  v_site_datatype_id AND 
            interval          =  v_interval         AND
            start_date_time   =  v_start_date_time  AND  
            end_date_time     =  v_end_date_time    AND     
            SOURCE            =  IN_SOURCE          ;        

        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_DATA;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_DATA for &&tbl_owner..XC_INSERT_OR_UPDATE_DATA;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_DATA to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_DATA_ONLY;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_DATA_ONLY ( 
            IN_STATION_ID     IN VARCHAR2 ,
            IN_SENSORNAME     IN VARCHAR2 ,
            IN_TIME_TAG       IN DATE     ,
            IN_SOURCE         IN VARCHAR2 ,
            IN_ORIG_VALUE     IN FLOAT    ,
            IN_ED_VALUE       IN FLOAT    
        )                                      
IS                                          
    v_rowcount          NUMBER;
    v_site_datatype_id  NUMBER;
    v_interval          VARCHAR2(16);
    v_overwrite_flag    VARCHAR2(1);         
    v_validation        CHAR;             
    v_agen_id           NUMBER;     
    v_collection_system_id      NUMBER;   
    v_loading_application_id    NUMBER;
    v_method_id         NUMBER;
    v_start_date_time	DATE;
    v_end_date_time     DATE;
    v_computation_id    NUMBER;     
    v_do_update_y_or_n  VARCHAR2(1);     

BEGIN

    SET TRANSACTION READ WRITE;
 
    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_EXT_SITESENSORS 
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);

    IF v_rowcount = 0 THEN
        &&tbl_hdb..DENY_ACTION ( 'Site Data Type ID does not exist: ' || IN_STATION_ID || '.' || IN_SENSORNAME);
    END IF;

    SELECT SITE_DATATYPE_ID, INTERVAL
    INTO v_site_datatype_id, v_interval
    FROM XC_EXT_SITESENSORS 
    WHERE UPPER(STE_STATION_ID) = UPPER(IN_STATION_ID) AND UPPER(SENSORNAME) = UPPER(IN_SENSORNAME);

    IF (v_site_datatype_id IS NULL) OR (v_interval IS NULL) THEN
        &&tbl_hdb..DENY_ACTION ( 'site_datatype_id or interval must not be NULL: ' || IN_STATION_ID || '.' || IN_SENSORNAME);
    END IF;

    &&tbl_owner..XC_GET_DATA_DEFAULT(
        v_site_datatype_id          ,
        v_interval                  ,
        v_overwrite_flag            ,
        v_validation                ,
        v_agen_id                   ,
        v_collection_system_id      ,
        v_loading_application_id    ,
        v_method_id                 ,
        v_computation_id            ,
        v_do_update_y_or_n          
    );

    v_start_date_time := IN_TIME_TAG;
    v_end_date_time := NULL;

    -- BEGIN Boulder HDB ONLY
    -- For all hourly data, as the data logger log the hourly data at the end of the hour
    -- make adjustment
    -- IF v_interval = 'hour' THEN 
    --     v_start_date_time := IN_TIME_TAG - 1/24;
    --     v_end_date_time := IN_TIME_TAG;
    -- END IF;
    -- END Boulder HDB ONLY

    &&tbl_hdb..modify_r_base_raw(
        v_site_datatype_id,     --SITE_DATATYPE_ID
        v_interval,             --INTERVAL            
        v_start_date_time,      --START_DATE_TIME         
        v_end_date_time,        --END_DATE_TIME           
        IN_ED_VALUE,            --VALUE                   
        v_agen_id,              --AGEN_ID                 
        v_overwrite_flag,       --OVERWRITE_FLAG          
        v_validation,           --VALIDATION              
        v_collection_system_id, --COLLECTION_SYSTEM_ID    
        v_loading_application_id,--LOADING_APPLICATION_ID  
        v_method_id,            --METHOD_ID               
        v_computation_id,       --COMPUTATION_ID          
        v_do_update_y_or_n      --DO_UPDATE_Y_OR_N        
    );

    SELECT COUNT(*) INTO v_rowcount
    FROM XC_EXT_DATA1
    WHERE 
            site_datatype_id  =  v_site_datatype_id AND 
            interval          =  v_interval         AND
            start_date_time   =  v_start_date_time  AND  
            end_date_time     =  v_end_date_time    AND     
            SOURCE            =  IN_SOURCE          ;        

    IF v_rowcount = 0 THEN
        INSERT INTO XC_EXT_DATA1(
            site_datatype_id,
            interval       ,
            start_date_time,
            end_date_time  ,
            SOURCE         ,            
            ORIG_VALUE                 
        )VALUES(
            v_site_datatype_id,         --site_datatype_id
            v_interval        ,         --interval        
            v_start_date_time ,         --start_date_time 
            v_end_date_time   ,         --end_date_time   
            IN_SOURCE         ,         --SOURCE          
            IN_ORIG_VALUE               --ORIG_VALUE         
        );
    END IF;
    COMMIT;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_DATA_ONLY;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_DATA_ONLY for &&tbl_owner..XC_INSERT_OR_UPDATE_DATA_ONLY;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_DATA_ONLY to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_GOESQC;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_GOESQC ( 
        IN_NESDIS_ID       IN VARCHAR2      ,
        IN_TIME_TAG        IN DATE          ,  
        IN_SOURCE          IN VARCHAR2      ,
        IN_STATION_ID      IN VARCHAR2      ,
        IN_FAILURE_CODE    IN VARCHAR2      ,
        IN_SIG_STRENGTH    IN NUMBER        ,
        IN_FREQ_OFFSET     IN NUMBER        ,
        IN_MOD_INDEX       IN VARCHAR2      ,
        IN_MOD_QUAL        IN VARCHAR2      ,
        IN_CHANNEL         IN NUMBER        ,
        IN_SAT_LOC         IN VARCHAR2      ,
        IN_UC_STATUS       IN VARCHAR2      ,
        IN_MESSAGE_LEN     IN NUMBER        ,  
        IN_ACTUAL_LEN      IN NUMBER        ,
        IN_PARITY          IN NUMBER        ,
        IN_BUFFER_EMPTY    IN NUMBER        ,
        IN_BATTERY_VOLTAGE IN FLOAT         )       
IS                                          
    v_rowcount NUMBER;                
BEGIN

    SET TRANSACTION READ WRITE;
 
    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_GOESQC
    WHERE 
            NESDIS_ID       =  IN_NESDIS_ID AND
            TIME_TAG        =  IN_TIME_TAG  AND
            SOURCE          =  IN_SOURCE    ;   
    
    IF v_rowcount = 0 THEN
        INSERT INTO XC_GOESQC(
            NESDIS_ID       ,
            TIME_TAG        ,
            SOURCE          ,
            STATION_ID      ,
            FAILURE_CODE    ,            
            SIG_STRENGTH    ,
            FREQ_OFFSET     ,
            MOD_INDEX       ,
            MOD_QUAL        ,   
            CHANNEL         ,    
            SAT_LOC         ,    
            UC_STATUS       ,    
            MESSAGE_LEN     ,    
            ACTUAL_LEN      ,    
            PARITY          ,    
            BUFFER_EMPTY    ,    
            BATTERY_VOLTAGE     
        )VALUES(
            IN_NESDIS_ID       ,
            IN_TIME_TAG        ,
            IN_SOURCE          ,
            IN_STATION_ID      ,
            IN_FAILURE_CODE    ,            
            IN_SIG_STRENGTH    ,
            IN_FREQ_OFFSET     ,
            IN_MOD_INDEX       ,
            IN_MOD_QUAL        ,   
            IN_CHANNEL         ,    
            IN_SAT_LOC         ,    
            IN_UC_STATUS       ,    
            IN_MESSAGE_LEN     ,    
            IN_ACTUAL_LEN      ,    
            IN_PARITY          ,    
            IN_BUFFER_EMPTY    ,    
            IN_BATTERY_VOLTAGE     
        );
        COMMIT;
    ELSE
        UPDATE XC_GOESQC SET
            STATION_ID      = STATION_ID     ,
            FAILURE_CODE    = FAILURE_CODE   ,            
            SIG_STRENGTH    = SIG_STRENGTH   ,
            FREQ_OFFSET     = FREQ_OFFSET    ,
            MOD_INDEX       = MOD_INDEX      ,
            MOD_QUAL        = MOD_QUAL       ,   
            CHANNEL         = CHANNEL        ,    
            SAT_LOC         = SAT_LOC        ,    
            UC_STATUS       = UC_STATUS      ,    
            MESSAGE_LEN     = MESSAGE_LEN    ,    
            ACTUAL_LEN      = ACTUAL_LEN     ,    
            PARITY          = PARITY         ,    
            BUFFER_EMPTY    = BUFFER_EMPTY   ,    
            BATTERY_VOLTAGE = BATTERY_VOLTAGE    
        WHERE                
            NESDIS_ID       =  IN_NESDIS_ID AND
            TIME_TAG        =  IN_TIME_TAG  AND
            SOURCE          =  IN_SOURCE    ;   
        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_GOESQC;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_GOESQC for &&tbl_owner..XC_INSERT_OR_UPDATE_GOESQC;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_GOESQC to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_RTUQC;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_RTUQC ( 
        IN_STATION_ID          IN VARCHAR2 ,
        IN_TIME_TAG            IN DATE     ,
        IN_RX_TX               IN VARCHAR2 ,
        IN_PORT                IN VARCHAR2 ,
        IN_SEQUENCENUM         IN NUMBER   ,
        IN_OPCODE              IN NUMBER   ,
        IN_STATUS              IN NUMBER   ,
        IN_RETRIES             IN NUMBER   ,
        IN_GOOD                IN NUMBER   ,
        IN_BAD                 IN NUMBER   ,
        IN_CRC                 IN NUMBER   ,
        IN_BATTERY_VOLTAGE     IN FLOAT    )
IS
    v_rowcount NUMBER;
BEGIN

    SET TRANSACTION READ WRITE;
 
    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_RTUQC
    WHERE 
            STATION_ID      =  IN_STATION_ID AND
            TIME_TAG        =  IN_TIME_TAG   AND
            RX_TX           =  IN_RX_TX      ;   
    
    IF v_rowcount = 0 THEN
        INSERT INTO XC_RTUQC(
            STATION_ID      ,
            TIME_TAG        ,
            RX_TX           ,
            PORT            ,
            SEQUENCENUM     ,            
            OPCODE          ,
            STATUS          ,
            RETRIES         ,
            GOOD            ,   
            BAD             ,    
            CRC             ,    
            BATTERY_VOLTAGE
        )VALUES(
            IN_STATION_ID     ,
            IN_TIME_TAG       ,
            IN_RX_TX          ,
            IN_PORT           ,
            IN_SEQUENCENUM    ,            
            IN_OPCODE         ,
            IN_STATUS         ,
            IN_RETRIES        ,
            IN_GOOD           ,   
            IN_BAD            ,    
            IN_CRC            ,    
            IN_BATTERY_VOLTAGE    
        );
        COMMIT;
    ELSE
        UPDATE XC_RTUQC SET
            PORT            = IN_PORT            ,
            SEQUENCENUM     = IN_SEQUENCENUM     ,
            OPCODE          = IN_OPCODE          ,   
            STATUS          = IN_STATUS          ,    
            RETRIES         = IN_RETRIES         ,    
            GOOD            = IN_GOOD            ,    
            BAD             = IN_BAD             ,    
            CRC             = IN_CRC             ,    
            BATTERY_VOLTAGE = IN_BATTERY_VOLTAGE 
        WHERE                
            STATION_ID      =  IN_STATION_ID AND
            TIME_TAG        =  IN_TIME_TAG   AND
            RX_TX           =  IN_RX_TX      ;   
        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_RTUQC;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_RTUQC for &&tbl_owner..XC_INSERT_OR_UPDATE_RTUQC;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_RTUQC to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_TXTDATA;

CREATE PROCEDURE &&tbl_owner..XC_INSERT_OR_UPDATE_TXTDATA ( 
        IN_STATION_ID      IN VARCHAR2 ,
        IN_SENSORNAME      IN VARCHAR2 ,
        IN_TIME_TAG        IN DATE     ,
        IN_SOURCE          IN VARCHAR2 ,
        IN_TEXT_VALUE      IN VARCHAR2 ,
        IN_FLAG1           IN VARCHAR2 ,
        IN_FLAG2           IN VARCHAR2 ,
        IN_FLAG3           IN VARCHAR2 ,
        IN_FLAG4           IN VARCHAR2 ,
        IN_MESSAGE_STATUS  IN VARCHAR2 ,
        IN_TXT_ALARM_FLAG  IN NUMBER   )
IS                                          
    v_rowcount NUMBER;                
BEGIN

    SET TRANSACTION READ WRITE;
 
    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_TXTDATA1
    WHERE 
            STATION_ID  =  IN_STATION_ID AND
            SENSORNAME  =  IN_SENSORNAME AND
            TIME_TAG    =  IN_TIME_TAG   AND   
            SOURCE      =  IN_SOURCE     ;

    IF v_rowcount = 0 THEN
        INSERT INTO XC_TXTDATA1(
            STATION_ID     ,
            SENSORNAME     ,
            TIME_TAG       ,
            SOURCE         ,
            TEXT_VALUE     ,            
            FLAG1          ,
            FLAG2          ,
            FLAG3          ,
            FLAG4          ,   
            MESSAGE_STATUS ,    
            TXT_ALARM_FLAG     
        )VALUES(
            IN_STATION_ID     ,
            IN_SENSORNAME     ,
            IN_TIME_TAG       ,
            IN_SOURCE         ,
            IN_TEXT_VALUE     ,            
            IN_FLAG1          ,
            IN_FLAG2          ,
            IN_FLAG3          ,
            IN_FLAG4          ,   
            IN_MESSAGE_STATUS ,    
            IN_TXT_ALARM_FLAG    
        );
        COMMIT;
    ELSE
        UPDATE XC_TXTDATA1 SET
            TEXT_VALUE     = IN_TEXT_VALUE       ,
            FLAG1          = IN_FLAG1            ,            
            FLAG2          = IN_FLAG2            ,
            FLAG3          = IN_FLAG3            ,
            FLAG4          = IN_FLAG4            ,
            MESSAGE_STATUS = IN_MESSAGE_STATUS   ,   
            TXT_ALARM_FLAG = IN_TXT_ALARM_FLAG       
        WHERE                
            STATION_ID  =  IN_STATION_ID AND
            SENSORNAME  =  IN_SENSORNAME AND
            TIME_TAG    =  IN_TIME_TAG   AND   
            SOURCE      =  IN_SOURCE     ;
        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INSERT_OR_UPDATE_TXTDATA;
CREATE PUBLIC SYNONYM XC_INSERT_OR_UPDATE_TXTDATA for &&tbl_owner..XC_INSERT_OR_UPDATE_TXTDATA;
GRANT EXECUTE ON &&tbl_owner..XC_INSERT_OR_UPDATE_TXTDATA to XCMANAGERS;


DROP PROCEDURE &&tbl_owner..XC_INS_OR_UPD_TXTDATA_ONLY;
CREATE PROCEDURE &&tbl_owner..XC_INS_OR_UPD_TXTDATA_ONLY ( 
        IN_STATION_ID      IN VARCHAR2 ,
        IN_SENSORNAME      IN VARCHAR2 ,
        IN_TIME_TAG        IN DATE     ,
        IN_SOURCE          IN VARCHAR2 ,
        IN_TEXT_VALUE      IN VARCHAR2 )
IS                                          
    v_rowcount NUMBER;                
BEGIN

    SET TRANSACTION READ WRITE;
 
    SELECT COUNT(*) 
    INTO v_rowcount
    FROM XC_TXTDATA1
    WHERE 
            STATION_ID  =  IN_STATION_ID AND
            SENSORNAME  =  IN_SENSORNAME AND
            TIME_TAG    =  IN_TIME_TAG   AND   
            SOURCE      =  IN_SOURCE     ;

    IF v_rowcount = 0 THEN
        INSERT INTO XC_TXTDATA1(
            STATION_ID     ,
            SENSORNAME     ,
            TIME_TAG       ,
            SOURCE         ,
            TEXT_VALUE     
        )VALUES(
            IN_STATION_ID     ,
            IN_SENSORNAME     ,
            IN_TIME_TAG       ,
            IN_SOURCE         ,
            IN_TEXT_VALUE     
        );
        COMMIT;
    ELSE
        UPDATE XC_TXTDATA1 SET
            TEXT_VALUE     = IN_TEXT_VALUE
        WHERE                
            STATION_ID  =  IN_STATION_ID AND
            SENSORNAME  =  IN_SENSORNAME AND
            TIME_TAG    =  IN_TIME_TAG   AND   
            SOURCE      =  IN_SOURCE     ;
        COMMIT;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN 
      ROLLBACK;
      RAISE;
END;
/

show errors;

DROP PUBLIC SYNONYM XC_INS_OR_UPD_TXTDATA_ONLY;
CREATE PUBLIC SYNONYM XC_INS_OR_UPD_TXTDATA_ONLY for &&tbl_owner..XC_INS_OR_UPD_TXTDATA_ONLY;
GRANT EXECUTE ON &&tbl_owner..XC_INS_OR_UPD_TXTDATA_ONLY to XCMANAGERS;


-- Triggers to validate site_datatype_id belongs to the correct site
-- NOTE: If the hdb_site.SITE_COMMON_NAME is not unique, remove this trigger.
CREATE OR REPLACE TRIGGER &&tbl_owner..XC_SITESENSORS_CHECK_SDI
BEFORE 
    INSERT OR 
    UPDATE OF SITE_DATATYPE_ID ON &&tbl_owner..XC_EXT_SITESENSORS
FOR EACH ROW 
declare
  v_rowcount            number;
BEGIN
    IF :NEW.site_datatype_id IS NOT NULL THEN 
        SELECT count( * )
        INTO v_rowcount
        FROM &&tbl_hdb..hdb_site_datatype A, &&tbl_hdb..hdb_site B
        WHERE
            (A.site_id = B.site_id) AND
            (A.site_datatype_id = :NEW.site_datatype_id) AND 
            (UPPER(B.SITE_COMMON_NAME) = UPPER(:NEW.STE_STATION_ID));
        
        IF v_rowcount = 0 THEN
            &&tbl_hdb..DENY_ACTION('hdb_site_datatype is not belong to station ' || :NEW.STE_STATION_ID || 
                '; New SDI: ' || :NEW.STE_STATION_ID);
        END IF;
    END IF; 
END;
/

show errors;


SPOOL OFF
