REM
REM Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
REM
REM  NAME
REM   cwm2sed2.sql
REM
REM  DESCRIPTION
REM   Seed script for adding initial system lookup information
REM   to AW_CREATE related metadata tables.
REM
REM  NOTES
REM
REM  MODIFIED  (MM/DD/YY)
REM     dbardwe 08/25/03 - bug 3065220 Average needs to be AVG
REM     mstasie 08/20/03 - 
REM     mstasie 06/30/03 - 
REM     mstasie 06/30/03 - 3026159
REM     mstasie 04/24/03 - 
REM     mstasie 03/13/03 - 
REM     dbardwe 02/10/03 - 
REM     dbardwe 02/10/03 - Need to check if row is present, if so do not add a dup row
REM     mstasie 01/07/03 - 
REM     mstasie 01/07/03 - 
REM     mstasie 12/23/02 - 
REM     mstasie 12/20/02 - 
REM     awesley 12/17/02 - 
REM     mstasie 11/20/02 - 
REM     awesley 11/19/02 - 
REM     awesley 11/08/02 - only load process code tables
REM     dbardwe 07/25/02 - 
REM     dbardwe 07/22/02 - dbardwel_txn103421
REM   dbardwel  07/11/02  Created.
REM
REM

declare
  next_id number;
  curr_cnt number;

function get_id return number is
 id number;
begin
 SELECT olap_IRID.nextval into id FROM dual;
 return id;
end get_id;


function add_dim_load_type(name_info varchar2, desc_info varchar2) return number is
 next_irid number;
begin
 next_irid := get_id;

 INSERT into cwm2$AWDimLoadType
       (IRID, NAME, DESCRIPTION)
      VALUES (next_irid, name_info, desc_info);

 return next_irid;
end add_dim_load_type;


function add_cube_load_type(name_info varchar2, desc_info varchar2) return number is
 next_irid number;
begin
 next_irid := get_id;

 INSERT into cwm2$AWCubeLoadType
      (IRID, NAME, DESCRIPTION)
     VALUES (next_irid, name_info, desc_info);

 return next_irid;
end add_cube_load_type;

function add_dim_load_parm(name_info varchar2, type_info varchar2, default_info varchar2) return number is
 next_irid number;
begin
 next_irid := get_id;

 INSERT into CWM2$AWDimLoadParm
      (IRID, NAME, DATATYPE, DEFAULT_VALUE)
     VALUES (next_irid, name_info, type_info, default_info);

 return next_irid;
end add_dim_load_parm;


function add_cube_load_parm(name_info varchar2, type_info varchar2, default_info varchar2) return number is
 next_irid number;
begin
 next_irid := get_id;

 INSERT into CWM2$AWCubeLoadParm
      (IRID, NAME, DATATYPE, DEFAULT_VALUE)
     VALUES (next_irid, name_info, type_info, default_info);

 return next_irid;
end add_cube_load_parm;


begin
 -- create entries for cwm2$AWDimLoadType
 execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadType where name = :1' into curr_cnt using 'FULL_LOAD';
 if curr_cnt = 0 then
   next_id := add_dim_load_type('FULL_LOAD', 'Dimension is fully loaded');
 end if;

 execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadType where name = :1' into curr_cnt using 'FULL_LOAD_ADDITIONS_ONLY';
 if curr_cnt = 0 then 
   next_id := add_dim_load_type('FULL_LOAD_ADDITIONS_ONLY', 'Dimension is loaded with additions only');
 end if;

 -- create entries for cwm2$AWCubeLoadType
 execute immediate 'select count(*) from olapsys.cwm2$AWCubeLoadType where name = :1' into curr_cnt using 'LOAD_DATA';
 if curr_cnt = 0 then
   next_id := add_cube_load_type('LOAD_DATA', 'Load lowest level data');
 end if;

 execute immediate 'select count(*) from olapsys.cwm2$AWCubeLoadType where name = :1' into curr_cnt using 'LOAD_PROGRAM';
 if curr_cnt = 0 then
   next_id := add_cube_load_type('LOAD_PROGRAM', 'Create Load Program but do not execute it.');
 end if;

 -- create entries for cwm2$AWDimLoadParm

 execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadParm where name = :1' into curr_cnt using 'UNIQUE_RDBMS_KEY';
 if curr_cnt = 0 then 
   next_id := add_dim_load_parm('UNIQUE_RDBMS_KEY', 'BOOLEAN', NULL);
 end if;

 execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadParm where default_value is not null and name = :1' into curr_cnt using 'UNIQUE_RDBMS_KEY';
 if curr_cnt >= 1 then
   update olapsys.cwm2$awdimloadparm set default_value = null
   where default_value is not null
   and name = 'UNIQUE_RDBMS_KEY';
   commit;
 end if;

 execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadParm where name = :1' into curr_cnt using 'DISPLAY_NAME';
 if curr_cnt = 0 then
   next_id := add_dim_load_parm('DISPLAY_NAME', 'VARCHAR2', NULL);
 end if;

 execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadParm where name = :1' into curr_cnt using 'P_DISPLAY_NAME';
 if curr_cnt = 0 then
   next_id := add_dim_load_parm('P_DISPLAY_NAME', 'VARCHAR2', NULL);
 end if;

execute immediate 'select count(*) from olapsys.cwm2$AWDimLoadParm where name = :1' into curr_cnt using 'DESCRIPTION';
 if curr_cnt = 0 then
   next_id := add_dim_load_parm('DESCRIPTION', 'VARCHAR2', NULL);
 end if;


 -- create entries for cwm2$AWCubeLoadParm
 execute immediate 'select count(*) from olapsys.CWM2$AWCubeLoadParm where name = :1' into curr_cnt using 'DISPLAY_NAME';
 if curr_cnt = 0 then 
   next_id := add_cube_load_parm('DISPLAY_NAME', 'VARCHAR2', NULL);
 end if;

-- Rename AVERAGE to AVG in cwm$function table
update cwm$function set name = 'AVG' where name = 'AVERAGE';

 commit;
end;

/