REM
REM Copyright (c) 2001, 2003, Oracle Corporation.  All rights reserved.  
REM
REM   NAME
REM    cwm2sed3.sql
REM
REM   DESCRIPTION
REM    populates new CWM2 metadata into classification system with
REM    new seed metadata for ODBO for 10g. Assumes that
REM    script cwm2seed.sql has been run already.
REM
REM   NOTES
REM    This is designed to work ok if run more than one time
REM    in the same instance. Subsequent runs should be a no-op.
REM
REM   MODIFIED  (MM/DD/YY)
REM     mstasiew 11/14/03 - 3258666
REM     mstasiew 09/25/03 - mstasiew_txn108712
REM     dbardwel 09/22/03 - Finalize new ODBO descriptor names
REM     dbardwel 09/16/03 - updates for descriptors
REM     dbardwel 09/09/02 - created for 10g ODBO support
REM

declare
  project_id number;
  desc_id number;
  dv_id number;
 
-- gets the next sequence number for CWM LITE 1 system
function get_id return number is
  id number;
begin
  SELECT olap_id_seq.nextval into id FROM dual;
  return id;
end;

procedure add_cwm$function(function_name varchar2) is
    function_id number;
begin
  begin
    select irid into function_id
    from cwm$function
    where name = function_name;
  exception
    when no_data_found then
    -- create function
    function_id := get_id;
    insert into cwm$function
    ( irid
    , name)
    values
    ( function_id
    , function_name);
  end;
end;


function add_descriptor(desc_type varchar2
                         , entity_type varchar2
                         , desc_description varchar2 := null) return number is
    clty_id number;
    obj_id number;
  begin
    -- get cwm$classificationtype id, creating if it does not exist
    begin
      SELECT irid INTO clty_id 
      FROM cwm$classificationtype
      WHERE name = desc_type;
    exception
      when no_data_found then
        clty_id := get_id;
        INSERT INTO cwm$classificationtype
        ( irid
        , project_irid
        , name
        , description)
        VALUES
        ( clty_id
        , project_id
        , desc_type
        , desc_description);
    end;
    
    -- create cwm$objecttype entry if does not exist
    begin
      SELECT irid INTO obj_id FROM cwm$objecttype
      WHERE name = entity_type AND classificationtype_irid = clty_id;
    exception
      when no_data_found then
        obj_id := get_id;
        INSERT INTO cwm$objecttype
        ( irid
        , project_irid
        , classificationtype_irid
        , name)
        VALUES
        ( obj_id
        , project_id
        , clty_id
        , entity_type);
    end;
    
    return clty_id;

  end;
  
function add_descriptor_value(desc_id number
                               , desc_value varchar2
                               , value_description  varchar2 := null) return number is
    cl_id number;
  begin
    -- get the descriptor, if it exists, if not exists - add it now.
    begin
      SELECT irid into cl_id from cwm$classification
      where name = desc_value and
            classificationtype_irid = desc_id;
    exception
      when no_data_found then
        -- create entry in cwm$classification for value
        cl_id := get_id;
        INSERT INTO cwm$classification
        ( irid
         , project_irid
         , classificationtype_irid
         , name
         , description)
         VALUES
        ( cl_id
         , project_id
         , desc_id
         , desc_value
         , value_description);
    end;

    return cl_id;
  end;

begin
  -- projects and catalog type
  select irid into project_id 
   from olapsys.cwm$project 
   where name = 'OLAP2';

  -- Create new classificationtypes for all ODBO-related metadata
  desc_id := add_descriptor('ODBO Level Type', 'LEVEL'
                          , 'Identifies the ODBO metadata for a Level');

  -- new classification entries for CWM2 metadata
  dv_id := add_descriptor_value(desc_id, 'Level Standard');  
  dv_id := add_descriptor_value(desc_id, 'Level Year');
  dv_id := add_descriptor_value(desc_id, 'Level HalfYear');
  dv_id := add_descriptor_value(desc_id, 'Level Quarter');
  dv_id := add_descriptor_value(desc_id, 'Level Month');
  dv_id := add_descriptor_value(desc_id, 'Level Week');
  dv_id := add_descriptor_value(desc_id, 'Level Day');
  dv_id := add_descriptor_value(desc_id, 'Level Hour');
  dv_id := add_descriptor_value(desc_id, 'Level Minute');
  dv_id := add_descriptor_value(desc_id, 'Level Second');

  -- Create new classificationtypes for all ODBO-related metadata
  desc_id := add_descriptor('ODBO Dimension Type', 'DIMENSION'
                          , 'Identifies the ODBO metadata for a Dimension');

  dv_id := add_descriptor_value(desc_id, 'Value Separator');

  desc_id := add_descriptor('ODBO Hierarchy Type', 'HIERARCHY'
                            , 'Identifies the ODBO metadata for a Hierarchy');

  dv_id := add_descriptor_value(desc_id, 'Skip Level');

  desc_id := add_descriptor('ODBO Measure Type', 'MEASURE'
                            , 'Identifies the ODBO metadata for a Measure');

  dv_id := add_descriptor_value(desc_id, 'Measure Format');
  dv_id := add_descriptor_value(desc_id, 'Measure Unit');

  -- now the extension type information like Default Members, DenseFlag, FactJoin, etc.
  desc_id := add_descriptor('ODBO Extension Type', 'EXTENSION',
                            'Identifies a specific extension for ODBO metadata');

  dv_id := add_descriptor_value(desc_id, 'Fact Table Join');
  dv_id := add_descriptor_value(desc_id, 'Default Member');
  dv_id := add_descriptor_value(desc_id, 'Dense Indicator');
  dv_id := add_descriptor_value(desc_id, 'Estimated Cardinality');
  add_cwm$function('NOAGG');

  commit;
end;
/