-- SQL script for generation of CERA Core 2.4
--     here: some views that are similar those in "views_grants.sql"
-- Practically, the views CITAT, INSTI, and PERSO in views_grants.sql
--     read from CERA tables Citation, Institute, and Person while 
--     these (NEW_CITAT, NEW_INSTI, and NEW_PERSO) read from (CITATION
--     union NEW_CITATION), (INSTITUTE union NEW_INSTITUTE), and
--     (PERSON union NEW_PERSON).            ft, 2000-07-03
--
-- Second: Views that bind an LOV XY with its load layer table NEW_XY
--     for all LOVs in CERA Core and afterwards for CERA MODULE 
--     DATA_ACCESS.
--
-- ----------------------------------------------
-- 
-- View to get the records of table (NEW_)CITATION more concentrated
create or replace view BOTH_CITAT as
select LTRIM(REPLACE(authors||':'||title||' ('||publication_date||') '||publication,'n/a')) c_name,
       citation_id, title, authors, publication, publisher, editor, publication_date, country,
       state, place, edition, access_spec, CITATION.presentation_id, presentation_descr,
       CITATION.citation_type_id, citation_type
  from CITATION, CITATION_TYPE, PRESENTATION
 where CITATION.presentation_id    = PRESENTATION.presentation_id
   and CITATION.citation_type_id   = CITATION_TYPE.citation_type_id
UNION
select LTRIM(REPLACE(authors||':'||title||' ('||publication_date||') '||publication,'n/a')) c_name,
       citation_id, title, authors, publication, publisher, editor, publication_date, country,
       state, place, edition, access_spec, NEW_CITATION.presentation_id, presentation_descr,
       NEW_CITATION.citation_type_id, citation_type
  from NEW_CITATION, CITATION_TYPE, PRESENTATION
 where NEW_CITATION.presentation_id    = PRESENTATION.presentation_id   
   and NEW_CITATION.citation_type_id   = CITATION_TYPE.citation_type_id ;
--
--
--
--
-- View to get the records of table (NEW_)INSTITUTE more concentrated
create or replace view BOTH_INSTI as
select institute_name||'-'||department_name||' ('||country||' '||place||')' i_name,
       institute_id, institute_name, institute_acronym, department_name, 
       department_acronym, country, state_or_province, place, street, street_postal_code, 
       pobox, pobox_postal_code, url, additional_info
  from INSTITUTE
UNION
select institute_name||'-'||department_name||' ('||country||' '||place||')' i_name,
       institute_id, institute_name, institute_acronym, department_name,
       department_acronym, country, state_or_province, place, street, street_postal_code,
       pobox, pobox_postal_code, url, additional_info
  from NEW_INSTITUTE ;
--
--
--
--
-- View to get the records of table (NEW_)PERSON more concentrated
create or replace view BOTH_PERSO as
select LTRIM(REPLACE(title||' '||first_name||' '||last_name||' ('||institute_acronym||')','n/a ')) p_name,
       LTRIM(REPLACE(last_name||', '||title||' '||first_name||' ('||institute_acronym||')','n/a ')) pl_name,
       person_id, first_name, second_name, last_name, title, PERSON.institute_id, telephone, fax, email, 
       PERSON.url, institute_acronym, i_name
  from PERSON, NEW_INSTI
 where NEW_INSTI.institute_id    = PERSON.institute_id
UNION
select LTRIM(REPLACE(title||' '||first_name||' '||last_name||' ('||institute_acronym||')','n/a ')) p_name,
       LTRIM(REPLACE(last_name||', '||title||' '||first_name||' ('||institute_acronym||')','n/a ')) pl_name,
       person_id, first_name, second_name, last_name, title, NEW_PERSON.institute_id, telephone, fax, email,
       NEW_PERSON.url, institute_acronym, i_name
  from NEW_PERSON, NEW_INSTI
 where NEW_INSTI.institute_id    = NEW_PERSON.institute_id ;
--
--
--
--
--
--

create or replace view BOTH_ACCESS_CONSTRAINT
    as select  *  from      ACCESS_CONSTRAINT
 union select  *  from  NEW_ACCESS_CONSTRAINT ;

create or replace view BOTH_ACCESS_TYPE 
    as select  *  from      ACCESS_TYPE
 union select  *  from  NEW_ACCESS_TYPE ;

create or replace view BOTH_AGGREGATION  
    as select  *  from      AGGREGATION
 union select  *  from  NEW_AGGREGATION ;

create or replace view BOTH_CITATION_TYPE
    as select  *  from      CITATION_TYPE
 union select  *  from  NEW_CITATION_TYPE ;

create or replace view BOTH_CONNECT_TYPE
    as select  *  from      CONNECT_TYPE
 union select  *  from  NEW_CONNECT_TYPE ;

create or replace view BOTH_CONTACT_TYPE
    as select  *  from      CONTACT_TYPE
 union select  *  from  NEW_CONTACT_TYPE ;

create or replace view BOTH_CURRENTNESS_REF
    as select  *  from      CURRENTNESS_REF
 union select  *  from  NEW_CURRENTNESS_REF ;

create or replace view BOTH_ENTRY_TYPE
    as select  *  from      ENTRY_TYPE
 union select  *  from  NEW_ENTRY_TYPE ;

create or replace view BOTH_FEES
    as select  *  from      FEES
 union select  *  from  NEW_FEES ;

create or replace view BOTH_FORMAT
    as select  *  from      FORMAT
 union select  *  from  NEW_FORMAT ;

create or replace view BOTH_GENERAL_KEY
    as select  *  from      GENERAL_KEY
 union select  *  from  NEW_GENERAL_KEY ;

create or replace view BOTH_HOR_COORD_SYS
    as select  *  from      HOR_COORD_SYS
 union select  *  from  NEW_HOR_COORD_SYS ;

create or replace view BOTH_LOCATION
    as select  *  from      LOCATION
 union select  *  from  NEW_LOCATION ;

create or replace view BOTH_PRESENTATION
    as select  *  from      PRESENTATION
 union select  *  from  NEW_PRESENTATION ;

create or replace view BOTH_PROGRESS
    as select  *  from      PROGRESS
 union select  *  from  NEW_PROGRESS ;

create or replace view BOTH_PROJECT
    as select  *  from      PROJECT
 union select  *  from  NEW_PROJECT ;

create or replace view BOTH_REF_TYPE
    as select  *  from      REF_TYPE
 union select  *  from  NEW_REF_TYPE ;

create or replace view BOTH_SPATIAL_DATA_ORG
    as select  *  from      SPATIAL_DATA_ORG
 union select  *  from  NEW_SPATIAL_DATA_ORG ;

create or replace view BOTH_TOPIC
    as select  *  from      TOPIC
 union select  *  from  NEW_TOPIC ;

create or replace view BOTH_UNIT
    as select  *  from      UNIT
 union select  *  from  NEW_UNIT ;

create or replace view BOTH_USE_CONSTRAINT
    as select  *  from      USE_CONSTRAINT
 union select  *  from  NEW_USE_CONSTRAINT ;

create or replace view BOTH_VER_COORD_SYS
    as select  *  from      VER_COORD_SYS
 union select  *  from  NEW_VER_COORD_SYS ;



-- starting CERA Module DATA_ACCESS

create or replace view BOTH_ACCESS_STRUCTURE
    as select  *  from      ACCESS_STRUCTURE
 union select  *  from  NEW_ACCESS_STRUCTURE ;

create or replace view BOTH_STORAGE
    as select  *  from      STORAGE
 union select  *  from  NEW_STORAGE ;

