-- This script retrieves most CERA information for the 
--      entry_id &1 as an overview
SET ECHO OFF
SET LINESIZE 130
SET PAGESIZE 5000
SET PAUSE OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET VERIFY OFF
-- tables ENTRY, PROGRESS
select entry_name from ENTRY where entry_id = &1;
select entry_acronym,progress_descr from ENTRY,PROGRESS 
 where entry_id=&1 and entry.progress_id=progress.progress_id(+);
select creation_date,review_date,revision_date,future_review_date 
  from ENTRY where entry_id = &1;

-- tables SUMMARY, QUALITY
select summary from ENTRY,SUMMARY 
 where entry_id=&1 and entry.summary_id=summary.summary_id;
select accuracy_report from ENTRY,QUALITY 
 where entry_id=&1 and entry.quality_id=quality.quality_id;
select consistency_report from ENTRY,QUALITY 
 where entry_id=&1 and entry.quality_id=quality.quality_id;
select completeness_report from ENTRY,QUALITY 
 where entry_id=&1 and entry.quality_id=quality.quality_id;
select horizontal_acc_report from ENTRY,QUALITY 
 where entry_id=&1 and entry.quality_id=quality.quality_id;
select vertical_acc_report from ENTRY,QUALITY 
 where entry_id=&1 and entry.quality_id=quality.quality_id;

-- tables PROJECT, GENERAL_KEY
select project_acronym from PROJECT,CAMPAIGN
 where CAMPAIGN.entry_id    = &1 
   and CAMPAIGN.project_id  = PROJECT.project_id;
select general_key from GENERAL_KEY,KEY_CONNECT
 where KEY_CONNECT.entry_id = &1 
   and KEY_CONNECT.general_key_id = GENERAL_KEY.general_key_id;

-- Block CONTACT
select contact_type||': '||title||' '||last_name||','||first_name 
       PERSON_INFO, institute_name||','||department_acronym||' '||place
       ||', '||street INSTITUTE_INFO
  from CONTACT,PERSON,INSTITUTE,CONTACT_TYPE
 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(+);

-- Block PUBLICATION
select authors||':'||title||' ('||citation_type||')'||publisher||' '||place
       ||' '||publication_date||' (presentation:'||presentation_descr||', '
       ||ref_type_descr||')'       PUBLICATION_INFO
  from REFERENCE,REF_TYPE,CITATION,PRESENTATION,CITATION_TYPE
 where REFERENCE.entry_id          = &1
   and REFERENCE.ref_type_id       = REF_TYPE.ref_type_id(+)
   and REFERENCE.citation_id       = CITATION.citation_id(+)
   and CITATION.presentation_id    = PRESENTATION.presentation_id (+)
   and CITATION.citation_type_id   = CITATION_TYPE.citation_type_id(+);

-- tables related to PARAMETER
select topic_name||'('||unit_acronym||') '||aggregation_descr||': '
       ||reference_method          PARAMETER_INFO
  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(+);

-- Block DISTRIBUTION
select 'USE: '||USE_CONSTRAINT.constraint_descr||';  ACCESS: '
       ||ACCESS_CONSTRAINT.constraint_descr  CONSTRAINT_INFO
  from DISTRIBUTION, USE_CONSTRAINT, ACCESS_CONSTRAINT
 where DISTRIBUTION.entry_id       = &1
   and DISTRIBUTION.use_constraint_id = USE_CONSTRAINT.use_constraint_id(+)
   and DISTRIBUTION.access_constraint_id=ACCESS_CONSTRAINT.access_constraint_id(+);
select 'FORMAT:'||format_acronym||' ACCESS_TYPE:'||access_acronym
       ||' FEES:'||fees_acronym||' SIZE:'||data_size||'Bytes'   DISTRIBUTION_INFO
  from DISTRIBUTION, FEES, FORMAT, ACCESS_TYPE
 where DISTRIBUTION.entry_id       = &1
   and DISTRIBUTION.fees_id        = FEES.fees_id(+)
   and DISTRIBUTION.format_id      = FORMAT.format_id(+)
   and DISTRIBUTION.access_type_id = ACCESS_TYPE.access_type_id(+);

-- tables related to SPATIAL_REFERENCE
select 'HORIZONTAL: '|| HOR_COORD_SYS.sys_descr ||
       '; VERTICAL: '|| VER_COORD_SYS.sys_descr  COORDINATE_SYS_INFO
  from HOR_COORD_SYS,VER_COORD_SYS,SPATIAL_REFERENCE
 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(+);

-- COVERAGE
select 'LAT:'||min_lat||'-'||max_lat||';   LON:'||min_lon||'-'||max_lon||
            ';   ALT:'||min_altitude||'-'||max_altitude         spatial_coverage,
       start_year||'.'||start_month||'.'||start_day||'-'||
        stop_year||'.'|| stop_month||'.'|| stop_day||'('||
        currentness_ref_descr||')'                              temporal_coverage
  from COVERAGE,TEMPORAL_COVERAGE,SPATIAL_COVERAGE,CURRENTNESS_REF
 where COVERAGE.entry_id  = &1
   and COVERAGE.spatial_coverage_id  = SPATIAL_COVERAGE.spatial_coverage_id
   and COVERAGE.temporal_coverage_id = TEMPORAL_COVERAGE.temporal_coverage_id
   and TEMPORAL_COVERAGE.currentness_ref_id(+) = CURRENTNESS_REF.currentness_ref_id;


-- table PROCESS_STEP
select process_date || ' (' || first_name || ' ' || last_name || '-' ||
       institute_acronym || ') ' || process_descr PROCESS_INFO
  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;

SET VERIFY ON
SET PAUSE ON
SET PAGESIZE 36
SET ECHO ON
