REM
REM Copyright (c) Oracle Corporation 2001. All Rights Reserved.
REM
REM   NAME
REM    cwm2seed.sql
REM
REM   DESCRIPTION
REM    populates new CWM2 metadata into classification system.
REM
REM   NOTES
REM
REM   MODIFIED  (MM/DD/YY)
REM     dbardwel 01/14/02 - dbardwel_txn101456 special attributes
REM     awesley  11/07/01 - awesley_txn100634
REM     dbardwel 11/01/01 - dbardwel_txn100562
REM     dbardwel 10/24/01 - Created
REM     mstasiew/dbardwel 01/30/02 - mstasiew_txn101701
REM

declare
  project_id number;
  type_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;

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
    -- 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);
    return cl_id;
  end;

begin
  -- projects and catalog type
  project_id := get_id;
  insert into cwm$project(irid, name)
   values(project_id, 'OLAP2');
  type_id := get_id;
  
  -- new classificationtype for all CWM2 Catalogs
  insert into cwm$classificationtype(irid, project_irid, name)
   values(type_id, project_id, 'ORACLE_OLAP2_CATALOG');

  SELECT irid into desc_id from cwm$classificationtype
   where NAME = 'Dimensional Attribute Descriptor';
  
  -- new classification entries for CWM2 metadata
  dv_id := add_descriptor_value(desc_id, 'Grouping ID');  
  dv_id := add_descriptor_value(desc_id, 'ET Key');
  dv_id := add_descriptor_value(desc_id, 'Parent ET Key');
  dv_id := add_descriptor_value(desc_id, 'Parent Grouping ID');

  commit;
end;
/