-- Script for raw data query of all CERA fields related to the entry_id &1
--     (The "(+)" are not necessary, when no NULL fields are present)
SET LINESIZE 130
SET PAGESIZE 5000
SET PAUSE OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET VERIFY OFF
SPOOL getRAW
-- --------------------------------------------------------------- 1: ENTRY
select *
  from ENTRY, PROGRESS, SUMMARY, ENTRY_TYPE, QUALITY
 where ENTRY.entry_id = &1
   and ENTRY.progress_id   = PROGRESS.progress_id (+)
   and ENTRY.summary_id    = SUMMARY.summary_id (+)
   and ENTRY.quality_id    = QUALITY.quality_id (+)
   and ENTRY.entry_type_id = ENTRY_TYPE.entry_type_id
       ;
-- --------------------------------------------------------------- 2: CONNE
select *
  from ENTRY, ENTRY1, ENTRY_CONNECT, CONNECT_TYPE, ENTRY_TYPE
 where ENTRY.entry_id = &1
   and ENTRY.entry_id              = ENTRY_CONNECT.gen_entry_id
   and ENTRY_CONNECT.spec_entry_id = ENTRY1.entry_id    
   and ENTRY1.entry_type_id        = ENTRY_TYPE.entry_type_id
   and ENTRY_CONNECT.connect_type_id = CONNECT_TYPE.connect_type_id
       ;
-- --------------------------------------------------------------- 3: KEY_C
select *
  from KEY_CONNECT, GENERAL_KEY
 where KEY_CONNECT.entry_id = &1
   and KEY_CONNECT.general_key_id    = GENERAL_KEY.general_key_id
       ;
-- --------------------------------------------------------------- 4: CAMPA
select *
  from CAMPAIGN, PROJECT
 where CAMPAIGN.entry_id = &1
   and CAMPAIGN.project_id    = PROJECT.project_id
       ;
-- --------------------------------------------------------------- 5: PARAM
select *
  from PARAMETER, TOPIC, UNIT, AGGREGATION, SPATIAL_DATA_ORG
 where PARAMETER.entry_id = &1
   and PARAMETER.topic_id      = TOPIC.topic_id (+)
   and PARAMETER.unit_id       = UNIT.unit_id (+)
   and PARAMETER.aggregation_id= AGGREGATION.aggregation_id (+)
   and PARAMETER.spatial_data_org_id = spatial_data_org.spatial_data_org_id (+)
       ;
-- --------------------------------------------------------------- 6: REFER
select *
  from REFERENCE, REF_TYPE, CITATION, CITATION_TYPE, PRESENTATION
 where REFERENCE.entry_id = &1
   and REFERENCE.ref_type_id   = REF_TYPE.ref_type_id (+)
   and REFERENCE.citation_id   = CITATION.citation_id (+)
   and   CITATION.citation_type_id = CITATION_TYPE.citation_type_id (+)
   and   CITATION.presentation_id  = PRESENTATION.presentation_id (+)
       ;
-- --------------------------------------------------------------- 7: CONTA
select *
  from CONTACT, CONTACT_TYPE, PERSON, INSTITUTE, INSTITUTE1
 where CONTACT.entry_id = &1
   and CONTACT.contact_type_id = CONTACT_TYPE.contact_type_id (+)
   and CONTACT.person_id       = PERSON.person_id (+)
   and CONTACT.institute_id    = INSTITUTE.institute_id (+)
   and PERSON.institute_id     = INSTITUTE1.institute_id (+)
       ;
-- --------------------------------------------------------------- 8: COVER
select *
  from COVERAGE, SPATIAL_COVERAGE, TEMPORAL_COVERAGE, CURRENTNESS_REF, 
       LOCATION_CONNECT, LOCATION, UNIT1, UNIT2
 where COVERAGE.entry_id = &1
   and COVERAGE.spatial_coverage_id  = SPATIAL_COVERAGE.spatial_coverage_id (+)
   and   SPATIAL_COVERAGE.min_alt_unit_id     = UNIT1.unit_id (+)
   and   SPATIAL_COVERAGE.max_alt_unit_id     = UNIT2.unit_id (+)
   and COVERAGE.temporal_coverage_id = TEMPORAL_COVERAGE.temporal_coverage_id (+)
   and   TEMPORAL_COVERAGE.currentness_ref_id = CURRENTNESS_REF.currentness_ref_id (+)
   and COVERAGE.entry_id  = LOCATION_CONNECT.entry_id (+)
   and   LOCATION_CONNECT.location_id = LOCATION.location_id (+)
       ;
-- --------------------------------------------------------------- 9: SPATI
select *
  from SPATIAL_REFERENCE, HOR_COORD_SYS, VER_COORD_SYS
 where SPATIAL_REFERENCE.entry_id = &1
   and SPATIAL_REFERENCE.hor_coord_sys_id = HOR_COORD_SYS.hor_coord_sys_id (+)
   and SPATIAL_REFERENCE.ver_coord_sys_id = VER_COORD_SYS.ver_coord_sys_id (+)
       ;
-- -------------------------------------------------------------- 10: DISTR
select *
  from DISTRIBUTION, FORMAT, ACCESS_TYPE, FEES, ACCESS_CONSTRAINT, USE_CONSTRAINT
 where DISTRIBUTION.entry_id = &1
   and DISTRIBUTION.format_id        = FORMAT.format_id (+)
   and DISTRIBUTION.access_type_id   = ACCESS_TYPE.access_type_id (+)
   and DISTRIBUTION.fees_id          = FEES.fees_id (+)
   and DISTRIBUTION.access_constraint_id = ACCESS_CONSTRAINT.access_constraint_id (+)
   and DISTRIBUTION.use_constraint_id= USE_CONSTRAINT.use_constraint_id (+)
       ;
-- -------------------------------------------------------------- 11: PROCE
select *
  from PROCESS_STEP, PERSON, INSTITUTE
 where PROCESS_STEP.entry_id = &1
   and PROCESS_STEP.person_id        = PERSON.person_id (+)
   and   PERSON.institute_id           = INSTITUTE.institute_id (+)
       ;
-- -------------------------------------------------------------- 12: DACCE
select *
  from PARAMETER, DATA_ACCESS, ACCESS_STRUCTURE,
       STORAGE1, STORAGE2, STORAGE3, STORAGE4
 where PARAMETER.entry_id = &1
   and PARAMETER.data_access_id      = DATA_ACCESS.data_access_id
   and   DATA_ACCESS.access_structure_id = ACCESS_STRUCTURE.access_structure_id (+)
   and   DATA_ACCESS.storage1_id       = STORAGE1.storage_id (+)
   and   DATA_ACCESS.storage2_id       = STORAGE2.storage_id (+)
   and   DATA_ACCESS.storage3_id       = STORAGE3.storage_id (+)
   and   DATA_ACCESS.storage4_id       = STORAGE4.storage_id (+)
       ;
-- -------------------------------------------------------------- 
SPOOL OFF
SET VERIFY ON
SET PAUSE ON
SET PAGESIZE 36
