--==============================================================================
--==============================================================================
--==============================================================================
--==============================================================================
--============     DO NOT USE THIS SCRIPT ANYMORE     ==========================
--============     TAKE THE EQUIVALENT FROM DKRZ!     ==========================
--============          ft,  2001-01-30               ==========================
--==============================================================================
--==============================================================================
--==============================================================================
--==============================================================================
-- This PL/SQL script inserts the contents of all tables named NEW_* into the
--    CERA LOVs + entities.
-- Make sure, that in entities NEW_PERSON, NEW_INSTITUTE, NEW_CITATION all
--    records are valid before starting. They are transferred unchecked.
-- Don't forget to purge all loading tables NEW_* after use of this 
--    script (and after having errorchecked loadCHECKEDvalues.lst) !
-- 2nd order LOV := LOV not connected to a LOAD_* but to another entity 
--    (two times CITATION in this case).

-- writing log record:
spool loadCHECKEDvalues
-- ------ first: CERA Core -----------------------------------------------------
-- ------------------------------------------- UPDATE OF LOVs: access_constraint
   insert into access_constraint(access_constraint_id, constraint_descr)
   select distinct NEW_access_constraint.access_constraint_id, 
                   NEW_access_constraint.constraint_descr 
     from NEW_access_constraint, LOAD_DISTR
    where LOAD_DISTR.access_constraint_descr = NEW_access_constraint.constraint_descr
      and LOAD_DISTR.access_constraint_descr NOT IN
          (select constraint_descr from access_constraint);
-- ------------------------------------------------- UPDATE OF LOVs: access_type
   insert into access_type(access_type_id, access_acronym, access_descr)
   select distinct NEW_access_type.access_type_id, 
                   NEW_access_type.access_acronym,
                   NEW_access_type.access_descr
     from NEW_access_type, LOAD_DISTR
    where LOAD_DISTR.access_acronym = NEW_access_type.access_acronym
      and LOAD_DISTR.access_acronym NOT IN
          (select access_acronym from access_type);
-- ------------------------------------------------- UPDATE OF LOVs: aggregation
   insert into aggregation(aggregation_id, aggregation_descr)
   select distinct NEW_aggregation.aggregation_id, 
                   NEW_aggregation.aggregation_descr 
     from NEW_aggregation, LOAD_PARAM
    where LOAD_PARAM.aggregation_descr = NEW_aggregation.aggregation_descr
      and LOAD_PARAM.aggregation_descr NOT IN
          (select aggregation_descr from aggregation);
-- -------------------------------------- UPDATE OF 2nd order LOV: citation_type
   insert into citation_type(citation_type_id, citation_type, citation_type_descr)
   select  NEW_citation_type.citation_type_id, 
           NEW_citation_type.citation_type,
           NEW_citation_type.citation_type_descr
     from NEW_citation_type;
--        following 4 lines cancelled, when NEW_CITATION  was set identical, ft, 2001-01-30
--   from NEW_citation_type, NEW_citation
--  where NEW_citation.citation_type = NEW_citation_type.citation_type
--    and NEW_citation.citation_type NOT IN
--        (select citation_type from citation_type);
-- not yet used at PIK ---------------------------- UPDATE OF LOVs: connect_type
-- insert into connect_type(connect_type_id, connect_type, connect_type_descr)
-- select distinct NEW_connect_type.connect_type_id, 
--                 NEW_connect_type.connect_type,
--                 NEW_connect_type.connect_type_descr
--   from NEW_connect_type, LOAD_ENTRY
--  where LOAD_ENTRY.connect_type = NEW_connect_type.connect_type
--    and LOAD_ENTRY.connect_type NOT IN
--        (select connect_type from connect_type);
-- ------------------------------------------------ UPDATE OF LOVs: contact_type
   insert into contact_type(contact_type_id, contact_type)
   select distinct NEW_contact_type.contact_type_id, 
                   NEW_contact_type.contact_type
     from NEW_contact_type, LOAD_CONTA
    where LOAD_CONTA.contact_type = NEW_contact_type.contact_type
      and LOAD_CONTA.contact_type NOT IN
          (select contact_type from contact_type);
-- --------------------------------------------- UPDATE OF LOVs: currentness_ref
   insert into currentness_ref(currentness_ref_id, currentness_ref_descr)
   select distinct NEW_currentness_ref.currentness_ref_id, 
                   NEW_currentness_ref.currentness_ref_descr
     from NEW_currentness_ref, LOAD_COVER
    where LOAD_COVER.currentness_ref_descr = NEW_currentness_ref.currentness_ref_descr
      and LOAD_COVER.currentness_ref_descr NOT IN
          (select currentness_ref_descr from currentness_ref);
-- -------------------------------------------------- UPDATE OF LOVs: entry_type
   insert into entry_type(entry_type_id, entry_type, entry_type_descr)
   select distinct NEW_entry_type.entry_type_id, 
                   NEW_entry_type.entry_type,
                   NEW_entry_type.entry_type_descr
     from NEW_entry_type, LOAD_ENTRY
    where LOAD_ENTRY.entry_type = NEW_entry_type.entry_type
      and LOAD_ENTRY.entry_type NOT IN
          (select entry_type from entry_type);
-- -------------------------------------------------------- UPDATE OF LOVs: fees
   insert into fees(fees_id, fees_acronym, fees_descr)
   select distinct NEW_fees.fees_id, 
                   NEW_fees.fees_acronym,
                   NEW_fees.fees_descr
     from NEW_fees, LOAD_DISTR
    where LOAD_DISTR.fees_acronym = NEW_fees.fees_acronym
      and LOAD_DISTR.fees_acronym NOT IN
          (select fees_acronym from fees);
-- ------------------------------------------------------ UPDATE OF LOVs: format
   insert into format(format_id, format_acronym, format_descr)
   select distinct NEW_format.format_id, 
                   NEW_format.format_acronym,
                   NEW_format.format_descr
     from NEW_format, LOAD_DISTR
    where LOAD_DISTR.format_acronym = NEW_format.format_acronym
      and LOAD_DISTR.format_acronym NOT IN
          (select format_acronym from format);
-- ------------------------------------------------- UPDATE OF LOVs: general_key
   insert into general_key(general_key_id, general_key)
   select distinct NEW_general_key.general_key_id, NEW_general_key.general_key
     from NEW_general_key, LOAD_KEY_C
    where LOAD_KEY_C.general_key  = NEW_general_key.general_key
      and lower(LOAD_KEY_C.general_key) NOT IN
          (select lower(general_key) from general_key);
-- ----------------------------------------------- UPDATE OF LOVs: hor_coord_sys
   insert into hor_coord_sys(hor_coord_sys_id, sys_descr)
   select distinct NEW_hor_coord_sys.hor_coord_sys_id, 
                   NEW_hor_coord_sys.sys_descr
     from NEW_hor_coord_sys, LOAD_SPATI
    where LOAD_SPATI.hor_sys_descr = NEW_hor_coord_sys.sys_descr
      and LOAD_SPATI.hor_sys_descr NOT IN
          (select sys_descr from hor_coord_sys);
-- ---------------------------------------------------- UPDATE OF LOVs: location
-- WARNING: The next two statements are PIK spcific. They append new records
--        that are not yet fixed in the tree under a "LOCATION" named "not yet
--        included into structure" which has location_id=810 at PIK.
--        Change _level=2 and _pointer=810 to the appropriate values or let (0,0).
   update NEW_location set location_pointer = 810 
    where location_pointer =   0 and location_level = 0;
   update NEW_location set location_level   = 2 
    where location_pointer = 810;
   insert into location(        location_id,                   location_pointer,
                                location_name,                 location_descr, 
                                location_level,                ref_date,
                                min_lat,  max_lat,  min_lon,  max_lon)
   select distinct NEW_location.location_id,      NEW_location.location_pointer,
                   NEW_location.location_name,    NEW_location.location_descr, 
                   NEW_location.location_level,   NEW_location.ref_date,
                                          NEW_location.min_lat, NEW_location.max_lat,
                                          NEW_location.min_lon, NEW_location.max_lon
     from NEW_location, LOAD_COVER, LOCATION
    where LOAD_COVER.location_name = NEW_location.location_name
      and NEW_location.location_name NOT IN
          (select location_name from LOCATION);
-- --------------------------------------- UPDATE OF 2nd order LOV: presentation
   insert into presentation(presentation_id, presentation_descr)
   select distinct NEW_presentation.presentation_id, 
                   NEW_presentation.presentation_descr
     from NEW_presentation, NEW_citation
    where NEW_citation.presentation_descr = NEW_presentation.presentation_descr
      and NEW_citation.presentation_descr NOT IN
          (select presentation_descr from presentation);
-- ---------------------------------------------------- UPDATE OF LOVs: progress
   insert into progress(progress_id, progress_descr)
   select distinct NEW_progress.progress_id, 
                   NEW_progress.progress_descr
     from NEW_progress, LOAD_ENTRY
    where LOAD_ENTRY.progress_descr = NEW_progress.progress_descr
      and LOAD_ENTRY.progress_descr NOT IN
          (select progress_descr from progress);
-- ----------------------------------------------------- UPDATE OF LOVs: project
   insert into project(project_id, project_name, project_acronym, project_descr)
   select distinct NEW_project.project_id, 
                   NEW_project.project_name,
                   NEW_project.project_acronym,
                   NEW_project.project_descr
     from NEW_project, LOAD_ENTRY
    where LOAD_ENTRY.project_acronym = NEW_project.project_acronym
      and LOAD_ENTRY.project_acronym NOT IN
          (select project_acronym from project);
-- ---------------------------------------------------- UPDATE OF LOVs: ref_type
   insert into ref_type(ref_type_id, ref_type_descr)
   select distinct NEW_ref_type.ref_type_id, 
                   NEW_ref_type.ref_type_descr
     from NEW_ref_type, LOAD_REFER
    where LOAD_REFER.ref_type_descr = NEW_ref_type.ref_type_descr
      and LOAD_REFER.ref_type_descr NOT IN
          (select ref_type_descr from ref_type);
-- -------------------------------------------- UPDATE OF LOVs: spatial_data_org
   insert into spatial_data_org(spatial_data_org_id, reference_method)
   select distinct NEW_spatial_data_org.spatial_data_org_id, 
                   NEW_spatial_data_org.reference_method
     from NEW_spatial_data_org, LOAD_PARAM
    where LOAD_PARAM.reference_method = NEW_spatial_data_org.reference_method
      and LOAD_PARAM.reference_method NOT IN
          (select reference_method from spatial_data_org);
-- ------------------------------------------------------- UPDATE OF LOVs: topic
-- WARNING: The next two statements are PIK spcific. They append new records
--        that are not yet fixed in the tree under a "TOPIC" named "not yet
--        included into the topic's tree" which has location_id=3000 at PIK.
--        Change _level=2 and _pointer=3000 to the appropriate values or let (0,0).
   update NEW_topic set topic_pointer= 3000  where topic_pointer=   0 and topic_level = 0;
   update NEW_topic set topic_level  = 2     where topic_pointer=3000 ;
-- Here follow two handy updates.
   update NEW_topic set topic_name   = topic_acronym          where topic_name    is NULL;
   update NEW_topic set topic_acronym=SUBSTR(topic_name,1,31) where topic_acronym is NULL;
-- Now we start:
   insert into topic(topic_id, topic_name, topic_acronym, topic_descr,
           topic_pointer, topic_level)
   select distinct NEW_topic.topic_id, 
                   NEW_topic.topic_name,
                   NEW_topic.topic_acronym,
                   NEW_topic.topic_descr,
                   NEW_topic.topic_pointer,
                   NEW_topic.topic_level
     from NEW_topic, LOAD_PARAM
    where LOAD_PARAM.topic_name = NEW_topic.topic_name
      and LOAD_PARAM.topic_name NOT IN
          (select topic_name from topic);
-- -------------------------------------------------------- UPDATE OF LOVs: unit
   insert into unit(unit_id, unit_name, unit_acronym, unit_descr)
   select distinct NEW_unit.unit_id, 
                   NEW_unit.unit_name,
                   NEW_unit.unit_acronym,
                   NEW_unit.unit_descr
     from NEW_unit, LOAD_PARAM
    where LOAD_PARAM.unit_name = NEW_unit.unit_name
      and LOAD_PARAM.unit_name NOT IN
          (select unit_name from unit);
-- ---------------------------------------------- UPDATE OF LOVs: use_constraint
   insert into use_constraint(use_constraint_id, constraint_descr)
   select distinct NEW_use_constraint.use_constraint_id, 
                   NEW_use_constraint.constraint_descr 
     from NEW_use_constraint, LOAD_DISTR
    where LOAD_DISTR.use_constraint_descr = NEW_use_constraint.constraint_descr
      and LOAD_DISTR.use_constraint_descr NOT IN
          (select constraint_descr from use_constraint);
-- ----------------------------------------------- UPDATE OF LOVs: ver_coord_sys
   insert into ver_coord_sys(ver_coord_sys_id, sys_descr)
   select distinct NEW_ver_coord_sys.ver_coord_sys_id, 
                   NEW_ver_coord_sys.sys_descr
     from NEW_ver_coord_sys, LOAD_SPATI
    where LOAD_SPATI.ver_sys_descr = NEW_ver_coord_sys.sys_descr
      and LOAD_SPATI.ver_sys_descr NOT IN
          (select sys_descr from ver_coord_sys);
-- -------------------------------------------------- UPDATE OF ENTITY: citation
   insert into CITATION (citation_id, title, authors, publication, publisher, 
          editor, publication_date, country, state, place, edition, access_spec,
          presentation_id, citation_type_id)
   select                citation_id, title, authors, publication, publisher, 
          editor, publication_date, country, state, place, edition, access_spec,
          presentation_id, citation_type_id
     from NEW_citation;
--        following 3 lines cancelled, when NEW_CITATION  was set identical
--                    to CITATION.     ft, 2000-07-05
--   from NEW_citation, presentation, citation_type
--  where NEW_citation.presentation_descr = presentation.presentation_descr (+)
--    and NEW_citation.citation_type      = citation_type.citation_type     (+);
-- ------------------------------------------------- UPDATE OF ENTITY: institute
   insert into INSTITUTE (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)
   select                 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;
-- ---------------------------------------------------- UPDATE OF ENTITY: person
   insert into PERSON (person_id, first_name, second_name, last_name,
          title, institute_id, telephone, fax, url, email)
   select              person_id, first_name, second_name, last_name,
          title, institute_id, telephone, fax, url, email
     from NEW_person;
-- ------ second: MODULE DATA_ACCESS -------------------------------------------
-- -------------------------------------------- UPDATE OF LOVs: access_structure
   insert into access_structure(access_structure_id, access_structure_name, 
          access_structure_descr)
   select distinct NEW_access_structure.access_structure_id, 
                   NEW_access_structure.access_structure_name,
                   NEW_access_structure.access_structure_descr
     from NEW_access_structure, LOAD_PARAM
    where LOAD_PARAM.access_structure_name = NEW_access_structure.access_structure_name
      and LOAD_PARAM.access_structure_name NOT IN
          (select access_structure_name from access_structure);
-- ---------------------------------------------------- UPDATE OF LOVs: storage1
   insert into storage(storage_id, storage_name)
   select distinct NEW_storage.storage_id, 
                   NEW_storage.storage_name
     from NEW_storage, LOAD_PARAM
    where LOAD_PARAM.storage1 = NEW_storage.storage_name
      and LOAD_PARAM.storage1 NOT IN
          (select storage_name from storage);
-- ---------------------------------------------------- UPDATE OF LOVs: storage2
   insert into storage(storage_id, storage_name)
   select distinct NEW_storage.storage_id, 
                   NEW_storage.storage_name
     from NEW_storage, LOAD_PARAM
    where LOAD_PARAM.storage2 = NEW_storage.storage_name
      and LOAD_PARAM.storage2 NOT IN
          (select storage_name from storage);
-- ---------------------------------------------------- UPDATE OF LOVs: storage3
   insert into storage(storage_id, storage_name)
   select distinct NEW_storage.storage_id, 
                   NEW_storage.storage_name
     from NEW_storage, LOAD_PARAM
    where LOAD_PARAM.storage3 = NEW_storage.storage_name
      and LOAD_PARAM.storage3 NOT IN
          (select storage_name from storage);
-- ---------------------------------------------------- UPDATE OF LOVs: storage4
   insert into storage(storage_id, storage_name)
   select distinct NEW_storage.storage_id, 
                   NEW_storage.storage_name
     from NEW_storage, LOAD_PARAM
    where LOAD_PARAM.storage4 = NEW_storage.storage_name
      and LOAD_PARAM.storage4 NOT IN
          (select storage_name from storage);
-- ----------------------------------------------- UPDATE OF LOVs: rec_structure
-- table REC_STRUCTURE is not used at PIK

spool off
